import ctypes
ctypes.windll.user32.ShowWindow(ctypes.windll.kernel32.GetConsoleWindow(), 0)
#上面两行确保批处理运行时不显示CMD窗口
import pymysql
import pymssql
import tkinter as tk
from tkinter import ttk
import tkinter.messagebox
import openpyxl as vb
from openpyxl.styles import numbers
from xlwt import easyxf
import getpass
import datetime
import difflib
import getpass
from openpyxl.styles import PatternFill
from openpyxl.styles import Alignment,Border,Side,Font
import numpy as np
from collections import Counter
from dateutil import relativedelta
# from dateutil.relativedelta import relativedelta
import time
import base64
from zeep import Client
import os
import re
import sys
import math
from icon_gif import img as icon
from email.mime.text import MIMEText
from email.header import Header
from email.mime.multipart import MIMEMultipart
from email.mime.image import MIMEImage
import matplotlib 
matplotlib.use("TKAgg") 
import matplotlib.pyplot as plt
import matplotlib.ticker as ticker
plt.rcParams['font.sans-serif'] = ['Microsoft YaHei']   
import traceback
import getpass

import ATA_test_plan_collection_v8 as test_collection
import ATA_charts_v1 as charts
import ATA_plan_sheet_recover as sheet_recover
import ATA_excel_output as excel_output



 
pre_date=(datetime.datetime.now()+datetime.timedelta(days=-14)).strftime('%Y-%m-%d') #邮件查询图表定义历史天数
todaycolumn=datetime.datetime.now().strftime('%Y-%m-%d') 



 

 
def old_upload():
  
  #1.获取当前日期
  today=datetime.datetime.now().strftime('%m/%d')
  todaycolumn=datetime.datetime.now().strftime('%Y-%m-%d') 
  tomorrowcolumn=(datetime.datetime.now()+datetime.timedelta(days=1)).strftime('%Y-%m-%d') 
  yesterdaycolumn=(datetime.datetime.now()+datetime.timedelta(days=-1)).strftime('%Y-%m-%d') 

 #临时为格式化Left day格式为整数，后续可以删除本段
  filepath = r'C:\自动分活儿\Reliability Test plan.xlsx'
  workbook = vb.load_workbook(filepath) 
  sheetnames = ['ALT','DVT','Accessory','Other']      
  for name in sheetnames:
    worksheet = workbook[name] 
    column = worksheet['T']
    for cell in column:
      cell.number_format = '0'
  # 保存工作簿
  workbook.save(filepath)

  
  try:
    #2.打开Excel,读取worksheet
    
    filepath = r'C:\自动分活儿\Reliability Test plan.xlsx'
    workbook = vb.load_workbook(filepath)  
    holiday_1stday='2050-01-01'  #提前定义一个大的日子，如果不是假期，那这个值永远不会有，下面做为导入条件就是没用的。
   
    #3.建立mysql的连接
    conn = pymysql.connect(host=ip_address,user='labuser',db='exceltodb',
            password='123456',port=3306,charset='utf8')
    # 获得游标
    cursor = conn.cursor()
 

    #4.DB-assignment表中删除日期为最大日期的记录，解决休假后再分活儿问题。20210520新增
    cursor.execute('''select max(date)from assignment''') 
    rows = cursor.fetchone()
   
    
    # print('check0')
    if str(rows[0])!=todaycolumn and str(rows[0])!=tomorrowcolumn: #如果最大日期不是今日（和明日：可能不需要明日这个条件，因为不可能，除非今天刚分完活儿）的情况下才删除最大日期，否则会产生不必要影响
      # print('yes')
      holiday_1stday=str(rows[0])  #20220206新增。如果DB中Date的最大值不是今天或明天，说明中间有假期，这个最大date值就是假期的第一天，赋给holiday_1stday      
      #下面这句只需要执行一次
      cursor.execute('''delete from assignment where Date=%s''',rows[0]) #本条只会在假期后第一次分活儿时执行，之后If条件就不会满足了
      # print('假期第一天：',holiday_1stday,type(holiday_1stday))
      conn.commit()
 
    cursor.execute('''delete from assignment where Date=%s or Date=%s''',(todaycolumn,tomorrowcolumn)) 
    conn.commit()
 
    print('A-1.准备导入今日正在进行的实验。连接数据库成功。')

    
    #开始导入excel 数据到DB：日期为今日，昨天分的实验。
    query = '''insert into assignment(Product,Test_no,Qty,Vintage,Test_category,Test_name,
    Status,Test_hour,Coefficient,Special,Operator,Lab_Engineer,Comments,Priority,ArrDate,Date,StartDate,
    ComDate,Left_day)values (%s,%s,%s, %s, %s, %s, %s, %s,%s,%s, %s, %s, %s, %s, %s,%s, %s, %s,%s)'''

    total_rows = 0
    sheetnames = ['ALT','DVT','Accessory','Other']
    
    
    for name in sheetnames:
      worksheet = workbook[name]
      maxrows=worksheet.max_row
      

  
      #针对DVT sheet， 只比对最后1000行数据，节约时间
      if name=='DVT' and maxrows-1100>=1:
        initialrows=maxrows-1100
      elif name=='ALT' and maxrows-80>=1:
        initialrows=maxrows-80
      elif name=='Accessory' and maxrows-50>=1:
        initialrows=maxrows-50
      elif name=='Other' and maxrows-40>=1:
        initialrows=maxrows-40
      else:
        initialrows=1      

       
      sheetzone=worksheet.iter_rows(min_row=initialrows,max_row=maxrows,min_col=1,max_col=22) # 逐行

 
      for row_excel in sheetzone:               
        Date = row_excel[18].value  #Date列     Date当时写入excel时，就是写入的str格式，因此不需要像下面startdate那样转换。 todaycolumn也是str类型
        Special=row_excel[12].value  
        StartDate = str(row_excel[8].value)[:10]  #openpyxl从excel导入的时间包含时分秒，先转成str然后再截取前面的年月日部分 

 
        
        if Date == todaycolumn or (Special=='a' and StartDate==todaycolumn) or Date==holiday_1stday:  #加a的实验必须人工写上开始日期（今日）才会被导入
          
          total_rows = total_rows + 1      #统计导入的今日实验行数                            
          Product = row_excel[1].value
          Test_no = row_excel[2].value
          Qty = row_excel[3].value
          Vintage = row_excel[4].value
          Test_category = row_excel[5].value
          Test_name = row_excel[6].value   
          ArrDate = row_excel[7].value
          StartDate = row_excel[8].value  
          ComDate = row_excel[9].value    
          Test_hour = row_excel[10].value                
          Coefficient = row_excel[11].value
          Special=row_excel[12].value   
          Priority = row_excel[13].value 
          Operator = row_excel[14].value
          Status = row_excel[15].value


          
          Lab_Engineer = row_excel[16].value
          Comments = row_excel[17].value
          # Date = row_excel[18].value
          # if Date==None:
          #   Date=todaycolumn     #a 实验的Date是空的，置为今日 解决special 实验为空问题
          Date=todaycolumn  #20211003 用这一行代替上面3行，解决放假时倒入的不是今日日期的问题。
          # print(Date)
          Left_day = row_excel[19].value
      
          if Status == 'Completed':    #人工强制完成的实验，导入，但是不再更新状态
            Left_day = 1       
        

          if Left_day==None:     #如果从Excel读到的Left_day为空，则置为1. 解决special 实验报错问题。
            Left_day=0        #20200101由1改成0，因为发现如果是标准实验，写了工时后，后面不会更新leftday, 而这里是1会导致更新状态会按照leftday最后一天更新。

          if Qty==None:     #如果从Excel读到的Qty为空，则置为0. 解决非标实验报错问题。
            Qty=0
          if Product==None:
            Product=''
          if Test_no==None:
            Test_no=''
          if Vintage ==None:
            Vintage=''
          if Test_category==None:
            Test_category=''
          if Test_name==None:
            Test_name=''
          if Status==None:
            Status=''
          if Test_hour==None:
            Test_hour=''
          if Coefficient==None:
            Coefficient=1
          if Special==None:
            Special=''
          if Operator==None:
            Operator=''
          if Priority==None:
            Priority=1      
          

          values =(Product,Test_no,Qty,Vintage,Test_category,Test_name,Status,Test_hour,Coefficient,
          Special,Operator,Lab_Engineer,Comments,Priority,ArrDate,Date,StartDate,ComDate,Left_day)

          # 执行sql语句
          cursor.execute(query,values) 
          conn.commit() 

      print(name,'该sheet导入完成')


    #Special='a'的标准实验，test_day 肯定是第一天，所以依据test_day=1来更新left_day. 如果是非标实验，则leftday之前已经置为1了，因此在下面一步也不会被选。
    cursor.execute('''update assignment set Left_day=(select left_day from testlist where test_day=1 and
    test_name like concat('%',(assignment.test_name),'%' )) where special='a' and test_hour ='' ''' )
    conn.commit()

    #check point
    print('A-2.读入昨日分活表成功。共',total_rows,'行。')
    
 
    cursor.execute('select * from assignment where Date=%s and left_day-1>0',todaycolumn)
    rows=cursor.fetchall()  #取出所有昨天未做完，且满足left_day-1>0条件的实验。

    for row in rows:  #逐行操作，一列列插入
    #0）读老的数据，写在下面，left_day-1 ,日期变更为明日.   到达日期，开始日期是不变的。  
      cursor.execute('''insert into assignment (Product,Test_No,Qty,Vintage,Test_category,Test_name,Status,Test_hour,Coefficient,
  Special,Operator,Lab_Engineer,Comments,Priority,ArrDate,Date,StartDate,ComDate,Left_day)values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s) ''',
      (row[1],row[2],row[3],row[4],row[5],row[6],row[7],row[8],row[9],row[10],row[11],row[12],row[13],row[14],row[15],tomorrowcolumn,row[17],row[18],row[19]-1))  
      conn.commit()
    #Special='s'的行，把Left_day-1的操作取消（又+1）； 把Special='s','c'的行，Operator 置为空。   
    cursor.execute('''update assignment set Left_day=Left_day+1 where Special='s' and Date= %s ''', tomorrowcolumn)
    cursor.execute('''update assignment set Operator='',Priority=1 where (Special='s' or Special='c')and Date= %s ''', tomorrowcolumn)
    #注意 update...set时，update后面的多项之间要用逗号，不能用and.
    
    conn.commit()

   # 标识为 a 的,不需要延续的也更新一下hour。 hour 为空的才更新，如果非空则不更新。
    cursor.execute(''' update assignment set Test_hour = (select test_hour from testlist where testlist.test_name
    like concat('%%',(assignment.test_name),'%%' )and testlist.Left_day=assignment.Left_day) where Special = 'a' and test_hour =''and Date=%s ''',(todaycolumn)) 
    
    #20210101新增：非标实验，如果没写工时，经上面操作后会使得工时为null, 这里置为0.
    cursor.execute('''update assignment set test_hour=0 where test_hour is Null and date=%s ''',todaycolumn)
    conn.commit()

 
    cursor.execute(''' update assignment set Test_hour = (select test_hour from testlist where testlist.test_name
    like concat('%%',(assignment.test_name),'%%' )and testlist.Left_day=assignment.Left_day) where Date=%s ''',(tomorrowcolumn)) 
    
    #check point
    print('A-3.筛选要延续的实验成功。')


    #b)更新test_day, 实验的第几天？(根据实验名字和剩余天数)
    cursor.execute('''update assignment set Test_day = (select test_day from testlist where testlist.test_name 
    like concat('%%',(assignment.test_name),'%%' )and testlist.Left_day=assignment.Left_day) where Date=%s or Date=%s ''',(todaycolumn,tomorrowcolumn))  
    print('temp check')

    #c)更新total_day, 实验总共天数。为了更新实验状态列（根据实验名字）
    cursor.execute('''update assignment set total_day = (select m from (select test_name as t, max(test_day) as m from testlist group by 
  test_name)as tmp where tmp.t like concat('%%',(assignment.test_name),'%%' )) where Date=%s or Date=%s ''',(todaycolumn,tomorrowcolumn)) 

     #check point
    print('A-4 .更新待延续实验的 test_day, total_day成功。')

    #d) 更新实验status(ALT,x of n, 当日完成)（根据实验名字，total_day, test_day)
    cursor.execute('''update assignment set status = '当日完成' where total_day=1 ''')   
    cursor.execute('''update assignment set status='Initial' where test_name like '%ALT%' and test_day=1 ''') 
    cursor.execute('''update assignment set status='Loop1' where test_name like '%ALT%' and test_day=2 ''') 
    cursor.execute('''update assignment set status='Loop2' where test_name like '%ALT%' and test_day=3 ''') 
    cursor.execute('''update assignment set status='Loop3&4' where (test_name like '%ALT 2019%' or test_name like '%ALT XXXX%') and test_day=4 ''') 
    cursor.execute('''update assignment set status='Loop5&Final' where (test_name like '%ALT 2019%' or test_name like '%ALT XXXX%') and test_day=5 ''') 

    cursor.execute('''update assignment set status='Loop3' where (test_name like '%ALT 2015%' or test_name like '%Charger ALT%' or test_name like '%Cable ALT%') and test_day=4 ''') 
    cursor.execute('''update assignment set status='Loop4' where (test_name like '%ALT 2015%' or test_name like '%Charger ALT%' or test_name like '%Cable ALT%') and test_day=5 ''') 
    cursor.execute('''update assignment set status='Loop5&Final' where (test_name like '%ALT 2015%' or test_name like '%Charger ALT%' or test_name like '%Cable ALT%') and test_day=6 ''') 

    cursor.execute('''update assignment set status= concat('Day',test_day,' of ',total_day) where total_day>1 and test_name NOT like '%ALT%' ''') 
    #5月7日新增，解决盐雾与ALT状态显示冲突问题。
    cursor.execute('''update assignment set status= concat('Day',test_day,' of ',total_day) where total_day>1 and test_name like '%Salt%' ''') 
     #check point
    print('A-5 .更新待延续实验状态 status 成功。')
    #5月6日新增
    cursor.execute('''update assignment set status='Initial' where test_name = 'ALT-US 2019' and test_day=1 ''') 
    cursor.execute('''update assignment set status='Loop1' where test_name = 'ALT-US 2019' and test_day=2 ''') 
    cursor.execute('''update assignment set status='Loop2' where test_name = 'ALT-US 2019' and test_day=3 ''') 
    cursor.execute('''update assignment set status='Loop3&4' where test_name = 'ALT-US 2019' and test_day=4 ''') 
    cursor.execute('''update assignment set status='Loop5&Final' where test_name = 'ALT-US 2019' and test_day=5 ''') 
    
    cursor.execute('''update assignment set status='Loop1' where test_name = 'ALT-NI 2019' and test_day=1 ''') 
    cursor.execute('''update assignment set status='Loop2' where test_name = 'ALT-NI 2019' and test_day=2 ''') 
    cursor.execute('''update assignment set status='Loop3&4' where test_name = 'ALT-NI 2019' and test_day=3 ''') 
    cursor.execute('''update assignment set status='Loop5&Final' where test_name = 'ALT-NI 2019' and test_day=4 ''') 

    conn.commit()

    print('A-6 .继续更新待延续实验状态 status 成功。')
    #统计时间为今日的实验数量：即刚刚导入的昨天分活表条数。
    cursor.execute('select count(*) from assignment where date=%s',(todaycolumn))
    a=cursor.fetchone()
    #统计时间为明日的实验数量：即需要明日继续做的实验条数。
    cursor.execute('select count(*) from assignment where date=%s',(tomorrowcolumn))
    b=cursor.fetchone()
    #统计时间为明日的实验数量：即需要明日继续做的实验条数。
    cursor.execute('''select count(*) from assignment where date=%s and Special='c' ''',(tomorrowcolumn))
    change=cursor.fetchone()
    cursor.execute('''select count(*) from assignment where date=%s and Special='s' ''',(tomorrowcolumn))
    stop=cursor.fetchone()

    #check point
    print('A-7. 待延续的实验状态更新完成！')     

    cursor.close()
    conn.close()  
    old_test_upload=(' 今日正在进行实验{}个! 待延续{}个，{}个需自动换人，{}个被暂停！'.format(a[0],b[0],change[0],stop[0])) 
    print('\n**** 正在进行实验处理完成:',old_test_upload) 
  except Exception:
    old_test_upload=('导入今日实验失败！！！分活儿无效！！！')

  return(old_test_upload) 



def new_upload():
          
  #1.获取当前日期
  todaycolumn=datetime.datetime.now().strftime('%Y-%m-%d') 
  tomorrow=(datetime.datetime.now()+datetime.timedelta(days=1)).strftime('%m%d')
  tomorrowcolumn=(datetime.datetime.now()+datetime.timedelta(days=1)).strftime('%Y-%m-%d') 
  tomorrowcolumn1=(datetime.datetime.now()+datetime.timedelta(days=1)).strftime('%Y-%m-%d') 
  pre_days=(datetime.datetime.now()+datetime.timedelta(days=-1)).strftime('%Y-%m-%d')
  #pre_days_upload 定义上传之前多久到达的实验. 20210819从30改成60天。
  pre_days_upload=(datetime.datetime.now()+datetime.timedelta(days=-60)).strftime('%Y-%m-%d')
  # 格式化pre_days_upload, 为了比较大小
  Xdaysago = datetime.datetime.strptime(pre_days_upload,"%Y-%m-%d") 
  #下面两行为了查找重复实验号使用
  pre_days_upload1=(datetime.datetime.now()+datetime.timedelta(days=-30)).strftime('%Y-%m-%d')  
  mdaysago = datetime.datetime.strptime(pre_days_upload1,"%Y-%m-%d") 
      
  #2.打开Excel,读取worksheet
  filepath = r'C:\自动分活儿\Reliability Test plan.xlsx'
  workbook = vb.load_workbook(filepath)
  
  sheet_j = workbook['未来考勤']
  y = sheet_j.max_row
  sheet_k = workbook['Data']

  #3.建立mysql的连接
  conn = pymysql.connect(host=ip_address,user='labuser',db='exceltodb',
          password='123456',port=3306,charset='utf8')
  # 获得游标
  cursor = conn.cursor()

  #check point
  print('B-1.准备导入待分新实验。连接数据库成功。')



  error=[]
  error1=[]
  try:
 
 
    cursor.execute('delete from namelist')
    query = '''insert into namelist(Employee_id,Employee_name,Estimatedhour1,Date,Estimatedhour2,Estimatedhour3,
    Estimatedhour4,Estimatedhour5,Estimatedhour6,Estimatedhour7,Estimatedhour8,Estimatedhour9,Estimatedhour10)values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)'''
    #把明日工时从Excel读出，写入namlist表格,加明日日期
    global names_onduty1
    names_onduty1=[]
    global names_onduty2
    names_onduty2=[]
    global names_onduty3
    names_onduty3=[]
    global names_onduty4
    names_onduty4=[]
    global names_onduty5
    names_onduty5=[]
    global names_onduty6
    names_onduty6=[]
    global names_onduty7
    names_onduty7=[]
    global names_onduty8
    names_onduty8=[]
    global names_onduty9
    names_onduty9=[]
    global names_onduty10
    names_onduty10=[]
    for r in range(2,y+1):      
      Employee_id = sheet_j.cell(r,1).value  
      Employee_name = sheet_j.cell(r,2).value
      Estimatedhour1 = sheet_j.cell(r,3).value 
      Estimatedhour2 = sheet_j.cell(r,4).value 
      Estimatedhour3 = sheet_j.cell(r,5).value 
      Estimatedhour4 = sheet_j.cell(r,6).value 
      Estimatedhour5 = sheet_j.cell(r,7).value 
      Estimatedhour6 = sheet_j.cell(r,8).value 
      Estimatedhour7 = sheet_j.cell(r,9).value 
      Estimatedhour8 = sheet_j.cell(r,10).value 
      Estimatedhour9 = sheet_j.cell(r,11).value 
      Estimatedhour10 = sheet_j.cell(r,12).value
        
      values =(Employee_id,Employee_name,Estimatedhour1,tomorrowcolumn,Estimatedhour2,Estimatedhour3,
      Estimatedhour4,Estimatedhour5,Estimatedhour6,Estimatedhour7,Estimatedhour8,Estimatedhour9,Estimatedhour10) 

      #明日上班名单放入names_onduty列表中(Estimatedhour>=1为在岗,没包括夜班人员)
      if Estimatedhour1>=1:
        names_onduty1.append(Employee_name)
      if Estimatedhour2>=1:
        names_onduty2.append(Employee_name)
      if Estimatedhour3>=1:
        names_onduty3.append(Employee_name)
      if Estimatedhour4>=1:
        names_onduty4.append(Employee_name)
      if Estimatedhour5>=1:
        names_onduty5.append(Employee_name)
      if Estimatedhour6>=1:
        names_onduty6.append(Employee_name)
      if Estimatedhour7>=1:
        names_onduty7.append(Employee_name)
      if Estimatedhour8>=1:
        names_onduty8.append(Employee_name)
      if Estimatedhour9>=1:
        names_onduty9.append(Employee_name)
      if Estimatedhour10>=1:
        names_onduty10.append(Employee_name)


      cursor.execute(query,values)        
      conn.commit()
  #check point
    print('B-2：清空数据库namelist表格并根据明日考勤表格更新每个人的Estimatedhour。')
 
    critical_test = '201'   # ALT2019,ALT2015
    cursor.execute('''update namelist set alt_hours = (select sum(test_hour*coefficient) from assignment where 
    assignment.Date=%s and assignment.test_name like %s and assignment.Operator = namelist.Employee_name group by operator) ''', (todaycolumn,('%'+critical_test+'%')))  
    conn.commit()    

   #check point
    print('B-3：从assignment中选出今日日期的ALT2019,2015，把工时更新到namelist中的alt_hours 中.')    


   #从namelist_copy 复制每个人对应的最大的一个alt_hours 加到 namelist. namelist中只是今日的alt_hours, namelist_copy中是昨天及之前每日的很多值，最大的代表截止到昨天的最大值。
    cursor.execute('''update namelist set alt_hours = alt_hours+(select max(alt_hours) from namelist_copy where date<%s and
     namelist_copy.Employee_name = namelist.Employee_name group by Employee_name) ''',tomorrowcolumn)  
    conn.commit()
   #check point
    print('B-4：从namelist_copy 复制每个人对应的最大的一个alt_hours 加到 namelist.')    
 
    critical_test1 = 'ESD' 
    critical_test2 = 'EDu03'       
    cursor.execute('''update namelist set esd_hours = (select sum(test_hour*coefficient) from assignment where 
    assignment.Date=%s and (assignment.test_name like %s or assignment.test_name like %s) and
     assignment.Operator = namelist.Employee_name group by operator) ''', (todaycolumn,('%'+critical_test1+'%'),('%'+critical_test2+'%')))  
    conn.commit()
    print('B-5: 从assignment中选出今日日期的ESD/Edu03，把工时更新到namelist中的esd_hours 中.!')
    
   #从namelist_copy 复制每个人对应的最大的一个esd_hours 加到 namelist
    cursor.execute('''update namelist set esd_hours = esd_hours+(select max(esd_hours) from namelist_copy where date<%s and
     namelist_copy.Employee_name = namelist.Employee_name group by Employee_name) ''',tomorrowcolumn)  
    conn.commit()
     #check point
    print('B-6：从namelist_copy 复制每个人对应的最大的一个esd_hours 加到 namelist.')   

 
    print('*B-7：把近两日工作负荷重的人，Estimatedhour-5 .。 此步骤在V2.0之后取消！')

    #针对延续实验，If special = 'cy', 则人员清空，等待人工分派。
    # cursor.execute('''update assignment set Operator=concat(operator,'->换人') where Special = 'cy' and Date=%s''', tomorrowcolumn)        
    cursor.execute('''update assignment set Operator='' where Special = 'cy' and Date=%s''', tomorrowcolumn)        
    conn.commit()

 #check point
    print('B-8：针对延续实验，If special = cy, 则人员清空，等待人工分派。。')

    #明天待延续实验：如果该人明天不在岗，且他的实验工作量非0，则标记Operator为空， special = 'cy'. 需人工分派。20210101改为自动分派. 20210627又改回人工分派
    cursor.execute('''select Operator, Test_hour*coefficient, Item,Priority,test_no from assignment where special !='s' and special !='c' and Date=%s ''',tomorrowcolumn)
    operators=cursor.fetchall()
    for o in operators:
      if o[1]!=0 and o[0] not in names_onduty1:    #item一个条件其实就够了
        # cursor.execute('''update assignment set Operator= concat(operator,'->换人'), Special='cy' where Item = %s and Operator = %s and Date=%s''',(o[2],o[0],tomorrowcolumn))
        # cursor.execute('''update assignment set Operator= concat(operator), Special='cy' where Item = %s and Operator = %s and Date=%s''',(o[2],o[0],tomorrowcolumn))  #20210629王锦倪昭希望人工加或者自动的cy后，把人清空，她们自己分派。
        cursor.execute('''update assignment set Operator= '', Special='cy' where Item = %s and Operator = %s and Date=%s''',(o[2],o[0],tomorrowcolumn))
        # cursor.execute('''update assignment set Operator= '', Special='cy', Priority = 1 where Item = %s and Operator = %s and Date=%s''',(o[2],o[0],tomorrowcolumn))
        # cursor.execute('''update assignment set Operator= '', Special='cy', Priority = 1 where Item = %s ''',o[2])  #20210627又改回人工分派（不用本行用第一行）
        conn.commit()

      else:
        continue

 #check point
    print('B-9：明天待延续实验：如果该人明天不在岗，且他的实验工作量非0，则清空Operator， special = cy, Priority=1. 等待自动分派。。')

 

    workbook = vb.load_workbook(filepath)
    #开始导入excel 待分实验数据到DB。之前步骤处理的都是延续实验。
    query = '''insert into assignment(Product,Test_no,Qty,Vintage,Test_category,Test_name,
        Coefficient,Special,Priority,Operator,Lab_Engineer,Comments,ArrDate,StartDate,Date,Left_day,
        Test_day)values (%s,%s,%s,%s, %s,%s, %s, %s, %s, %s,%s,%s, %s,%s,%s,%s, %s) '''
    
    total_rows = 0
    current_rows=0
    sheetnames = ['ALT','DVT','Accessory','Other']
    for name in sheetnames:
      worksheet = workbook[name]
      maxrows = worksheet.max_row  
      # print(worksheet,'总行数：',maxrows)    
      for r in range(2,maxrows+1):          
        arrive_date = worksheet.cell(r,8).value
        StartDate = worksheet.cell(r,9).value
        Special=worksheet.cell(r,13).value  
        Status = worksheet.cell(r,16).value    
        # print(arrive_date,Xdaysago,type(arrive_date),worksheet.cell(r,3).value)          
        if arrive_date is not None and arrive_date > Xdaysago and Status is None and Special!='a': #不能把a实验当做新实验导入
          total_rows = total_rows + 1                                        
          Product = worksheet.cell(r,2).value
          Test_no = worksheet.cell(r,3).value
          Qty = worksheet.cell(r,4).value
          Vintage = worksheet.cell(r,5).value
          Test_category = worksheet.cell(r,6).value
          Test_name = worksheet.cell(r,7).value                    
          Coefficient = worksheet.cell(r,12).value
          Special=worksheet.cell(r,13).value   
          Priority = worksheet.cell(r,14).value 
          Operator = worksheet.cell(r,15).value
          Lab_Engineer = worksheet.cell(r,17).value
          Comments = worksheet.cell(r,18).value
          
          ArrDate = worksheet.cell(r,8).value
          StartDate = worksheet.cell(r,9).value   
          Left_day = 0   #使得新数据和老数据能区分开
          Test_day = 1   #使得新数据和老数据能区分开
          if Qty=='':     #如果从Excel读到的Qty为空，则置为0. 解决非标实验报错问题。
            Qty=0
          if Coefficient==None:
            Coefficient = 0.8
          if Special==None:
            Special=''
          if Operator==None:
            Operator=''       
          # if Operator==None:    #b不知道为什么不管用，只能导入后执行了 
          print('已导入新实验行数：',total_rows,' 到达日期：',str(ArrDate)[5:10],Test_no,Test_name,'优先级：',Priority)  

          
          # if StartDate==None:  #前面已经是只导入startDate 为空的，此句可以不写
          StartDate=tomorrowcolumn  #不管里面是否为空，一律置位明天日期

          values =(Product,Test_no,Qty,Vintage,Test_category,Test_name,Coefficient,
          Special,Priority,Operator,Lab_Engineer,Comments,ArrDate,StartDate,tomorrowcolumn,Left_day,Test_day)
          # 执行sql语句
          cursor.execute(query,values) 
          conn.commit() 
        #汇总每个sheet导入的实验个数：

      print('导入：',worksheet,total_rows-current_rows,'行（个）；')
      print('------------------------------')
      current_rows=total_rows

    
                  
     #check point
    
    print('B-10：读取待分新实验写入数据库成功。共导入',total_rows,'行（个）。')
   


    #20210918新增：导入待分新后马上检查实验名是否正确
    #先从testlist取出实验名单，存在testnamelist中
    testnamelist=[]
    templist0=[]
   
    
    cursor.execute('''select test_name from testlist where test_day=1''')
    testname_info=cursor.fetchall()
    for i in testname_info:
      testnamelist.append(i[0])  
     
 
      
    print('B-11：实验名正确性检查完毕（取消）！')

    
 
    cursor.execute('''update assignment set left_day=(select m from(select test_name,max(Left_day)as m from testlist 
    group by test_name)as temp where temp.test_name like concat('%',assignment.test_name,'%'))
    where (Special='y' or Operator='')and Special!='c' and Special!='s' and Left_day<1 ''')   
    conn.commit()

    print('B-12：根据test_name更新最大Left_day完毕！')

    #b)根据test_name and Left_day,更新test_hour (Special='s','c'的也会重复更新一次，但没有关系)
    cursor.execute('''update assignment set Test_hour = (select test_hour from testlist where testlist.test_name 
    like concat('%',(assignment.test_name),'%' )and testlist.Left_day=assignment.Left_day)where Special='y' or Operator='' ''')  
    #针对S后继续进行，且指定人（选y)的老实验，填上人名，但是上传的工时清零。
    cursor.execute('''update assignment set test_hour=0 where Special='y'and Date=%s ''',todaycolumn)   
    conn.commit()

    print('B-13：根据test_name and Left_day,更新test_hour完毕！')

    #C)更新test_day/total_day/Priority: test_day=1, total_day=Left_day, Priority=3（默认设3)。(Special='s','c'的不更新)
    # 延续的实验一般不需要换人重新分配，所以也不用更新priority。但是需要换人的C实验，会在下面把Priority=1
    #priority 和 totalday 要分开设，否则有冲突，带有priority的人不会更新total day
    cursor.execute('''update assignment set total_day=Left_day where (Special='y' or Operator='') and 
    Special!='c' and Special!='s' and Test_day=1 and Date=%s ''',tomorrowcolumn)  
    conn.commit()
    cursor.execute('''update assignment set Priority = 3 where (Special='y' or Operator='') and 
    Special!='c' and Special!='s' and Test_day=1 and (Priority is NULL or Priority='') and Date=%s ''',tomorrowcolumn)  #如果之前priority 有人工设置的，就不设了。
    conn.commit()
    print('B-14:更新Total_day,设置Priority=3（默认设3)') 
    #对于待分新实验：非重要实验都设为4，一般实验是3.  
    cursor.execute('''update assignment set Priority = 4 where Priority =3 and (Test_category ='others' or Test_category ='FPS' or 
    Test_category ='TP' or Test_category ='LCM' or Test_category = 'Charger' or Test_category = 'Cable'  or Test_category = 'Headset' or Test_category = 'Pad') and Date=%s''',tomorrowcolumn)   
    conn.commit()    
    print('B-15：非重要实验默认设置Priority=4') 
    #重要实验设成2, c实验设成1
    cursor.execute('''update assignment set Priority = 1 where Priority =3 and (Test_category ='Hi-Priority' or Test_category ='1stORT' or Test_category ='TMO' or Test_category ='ATT') and Date=%s ''', tomorrowcolumn) 
    conn.commit()
    
    cursor.execute('''update assignment set Priority = 1 where (Priority ='' or Priority is NULL) and Special = 'c' and Date=%s''',tomorrowcolumn)  #c实验默认要继续
    conn.commit()
   
    print('B-16：更新Priority成功!')

    #c)更新新导入的待分新实验status(ALT,x of n, 当日完成)(Special='s','c'的不更新)
    cursor.execute('''update assignment set status = '当日完成' where total_day=1 and (Operator=''or Special='y')and Special!='c' and Special!='s' ''')   
    cursor.execute('''update assignment set status='Initial' where test_name like '%ALT%' and test_day=1 and (Special='y' or Operator='')and Special!='c' and Special!='s' ''') 
    cursor.execute('''update assignment set status='Loop1' where test_name like '%ALT%' and test_day=2 and (Special='y' or Operator='') and Special!='c' and Special!='s' ''') 
    cursor.execute('''update assignment set status='Loop2' where test_name like '%ALT%' and test_day=3 and (Special='y' or Operator='') and Special!='c' and Special!='s' ''') 
    cursor.execute('''update assignment set status='Loop3&4' where test_name like '%ALT 2019%' and test_day=4 and (Special='y' or Operator='') and Special!='c' and Special!='s' ''') 
    cursor.execute('''update assignment set status='Loop5&Final' where test_name like '%ALT 2019%' and test_day=5 and (Special='y' or Operator='')and Special!='c' and Special!='s' ''') 
    
    cursor.execute('''update assignment set status='Loop3' where (test_name like '%ALT 2015%' or test_name like '%Charger ALT%' or test_name like '%Cable ALT%') and test_day=4 ''') 
    cursor.execute('''update assignment set status='Loop4' where (test_name like '%ALT 2015%' or test_name like '%Charger ALT%' or test_name like '%Cable ALT%') and test_day=5 ''') 
    cursor.execute('''update assignment set status='Loop5&Final' where (test_name like '%ALT 2015%' or test_name like '%Charger ALT%' or test_name like '%Cable ALT%') and test_day=6 ''') 

    cursor.execute('''update assignment set status= concat('Day',test_day,' of ',total_day) where total_day>1 and test_name NOT like '%ALT%' and (Special='y' or Operator='') and Special!='c' and Special!='s' ''') 
        #5月7日新增，解决盐雾与ALT状态显示冲突问题。
    cursor.execute('''update assignment set status= concat('Day',test_day,' of ',total_day) where total_day>1 and test_name like '%Salt%' and (Special='y' or Operator='') and Special!='c' and Special!='s' ''') 

    #5月6日新增
    cursor.execute('''update assignment set status='Initial' where test_name = 'ALT-US 2019' and test_day=1 ''') 
    cursor.execute('''update assignment set status='Loop1' where test_name = 'ALT-US 2019' and test_day=2 ''') 
    cursor.execute('''update assignment set status='Loop2' where test_name = 'ALT-US 2019' and test_day=3 ''') 
    cursor.execute('''update assignment set status='Loop3&4' where test_name = 'ALT-US 2019' and test_day=4 ''') 
    cursor.execute('''update assignment set status='Loop5&Final' where test_name = 'ALT-US 2019' and test_day=5 ''') 

    #202311月15日新增
    cursor.execute('''update assignment set status='Initial' where test_name = 'ALT-C 2019' and test_day=1 ''') 
    cursor.execute('''update assignment set status='Loop1' where test_name = 'ALT-C 2019' and test_day=2 ''') 
    cursor.execute('''update assignment set status='Loop2' where test_name = 'ALT-C 2019' and test_day=3 ''') 
    cursor.execute('''update assignment set status='Loop3&4' where test_name = 'ALT-C 2019' and test_day=4 ''') 
    cursor.execute('''update assignment set status='Loop5&Final' where test_name = 'ALT-C 2019' and test_day=5 ''') 

    cursor.execute('''update assignment set status='Loop1' where test_name = 'ALT-NI 2019' and test_day=1 ''') 
    cursor.execute('''update assignment set status='Loop2' where test_name = 'ALT-NI 2019' and test_day=2 ''') 
    cursor.execute('''update assignment set status='Loop3&4' where test_name = 'ALT-NI 2019' and test_day=3 ''') 
    cursor.execute('''update assignment set status='Loop5&Final' where test_name = 'ALT-NI 2019' and test_day=4 ''') 

    conn.commit()

    #check point
    print('B-17：更新实验状态成功。')

    #正式分活儿前统计每个人工时。
    #需要延续进行的老实验，选出所有date列最大（为明日），且operator列为非空的条件，汇总提取出相关人员workload：Sum(test_hour*coefficient)写入namelist中。
    cursor.execute('''select sum(test_hour*coefficient),operator from assignment where Date=(select max(Date) from assignment) and operator!='' group by Operator ''')
    workloadold=cursor.fetchall()
    query=('update namelist set workload=%s where Employee_name=%s')
    
    for w in workloadold:

      workload=w[0]
      Employee_name=w[1]
      values=(workload,Employee_name)
      cursor.execute(query,values)
      conn.commit()     

      #在正式分活儿前统计每人的workload, >10.5时报警。这里包含继续进行的和special指定了人的新实验。
      print('workload is:',workload,Employee_name)
      if workload>10.5:    
        error.append(' * 分活儿前，{}的明日工时{:.1f}超过10.5小时，请处理！！！'.format(Employee_name,workload))
    for e in error:
      print(e)
   

    #统计待分实验总数,operator=''
    cursor.execute('''select count(*) from assignment where Operator='' and Special!='s' and Special!='y' and Date=%s ''',(tomorrowcolumn))
    a=cursor.fetchone()
    #统计明日实验总工时（包含待分实验，及明日继续实验，还有special tests.)
    cursor.execute('''select sum(test_hour*Coefficient) from assignment where Special!='s' and Date=%s''',(tomorrowcolumn))
    m=cursor.fetchone()    


     
    new_test_upload= (' 导入{}个待分新实验...... '.format(total_rows))  
    # new_test_upload= (' 导入{}行待分新实验! 明日待分共{}个，明日工时{:.1f}小时！'.format(total_rows,a[0],m[0]))  
    
     #check point
    print('B-18：分活儿前的数据处理工作完成:',new_test_upload)

 
  except Exception as e:
    new_test_upload=('待分新实验导入出错！！！分活儿无效！！！')  
    # error=('')
    traceback.print_exc()
 
  testlist=[]
  # testnamelist=[]
  namelist=[]

  # templist0=[]


 #取出operator名单,用来判断operator中填写的名字是否正确
  cursor.execute('''select employee_name from namelist''')
  name_info=cursor.fetchall()
  for i in name_info:
    namelist.append(i[0])
 
  cursor.execute('''SELECT arrdate,test_no,lab_engineer,Operator,Test_name, Test_category,Date,Special,startdate,test_hour FROM assignment WHERE DATE=%s or DATE=%s''',(tomorrowcolumn,todaycolumn)) 
  fill_info=cursor.fetchall()


  for x in fill_info:
    # print(x[0],x[1],x[2],x[3],x[4])
    if x[1] is None or len(x[1])<8: 
      error1.append(' *STOP:实验号错误: %s,%s,%s,%s'%(x[1],x[5],x[4],x[2]))
    if x[2] is None: 
      print(x)
      error1.append(' *STOP:到达日期:%s,%s,名为:%s 的实验无工程师! %s,Date: %s'%(x[0],x[1],x[4],x[5],x[6]))
    if x[3] not in namelist and x[3]!='': 
      error1.append(' *STOP:%s,%s, Operator:%s  --> 该人名不存在! '%(x[1],x[4],x[3]))

    

  # error1.append('  Stop!')
  #20221208 修改：把test_name =补工时 实验不再做为查重的对象
  cursor.execute('''select test_no from assignment where Date>%s and Date >'2023-05-10' and test_day=1 and special !='s' ''', mdaysago) 
  #将获取到的sql数据全部显示出来
  allrows=cursor.fetchall()  
  for test in allrows:
      if test[0]!='' and test[0] is not None:
          testlist.append(test[0])
  myset = set(testlist)  #以testlist为基础，生成一个新的列表，只是去重。
  for item in myset:
    n=testlist.count(item)   #统计item在列表testlist中出现的次数
    if n>1:
      error1.append(' 实验号%s 重复 %s 次! 程序终止！！！'%(item,n))
      cursor.execute('''select product, test_no, test_name, status, special, operator, Lab_engineer,Date, ArrDate from assignment where test_no = %s and Date>%s and test_day=1 and special !='s' ''', (item,mdaysago))   
      alltests=cursor.fetchall() 
      for test in alltests:
        print('实验号重复：',test[0],test[1],test[2],test[3],test[4],test[5],test[6],test[7],'Arrdate:',test[8])


 #20221030新增，将test plan 表格中Data sheet中的产品名称导入DB productlist,用于确保产品名称书写的规范性：
  cursor.execute('delete from productlist')
  query = '''insert into productlist(product,date)values(%s,%s)'''
  for i in range(2,500):
    cell = sheet_k.cell(row = i, column =1).value
    if cell is not None:
      # print(cell)
      values =(cell,time.strftime('%y/%m/%d %H:%M:%S'))
      cursor.execute(query,values)        
      conn.commit()
 #20221118新增，将test plan 表格中Data sheet中的实验名称导入DB test_name_list,用于确保test name名称书写的规范性：
 #20240902新增：把Data中的category, vintage也导进 test_name_list表格中
  cursor.execute('delete from test_name_list')
  query = '''insert into test_name_list(test_name,test_category,vintage,date)values(%s,%s,%s,%s)'''
  for i in range(2,700):
    cell_name = sheet_k.cell(row = i, column =2).value
    cell_category = sheet_k.cell(row = i, column =3).value
    cell_vintage = sheet_k.cell(row = i, column =6).value
    if cell_name is not None:
      # print(cell)
      values =(cell_name,cell_category,cell_vintage,time.strftime('%y/%m/%d %H:%M:%S'))
      cursor.execute(query,values)        
      conn.commit()

  print('B-19：导入test plan 表格中Data sheet中的产品名称及实验简称到数据库,用于校验名称书写的规范性！')
#20221030新增：导入待分新后马上检查产品名是否正确
  #先从productlist取出实验名单，存在productnamelist中
  productnamelist=[]
  testnamelist=[]
  category_list=[]
  vintage_list=[]  
  templist0=[]

 #验证产品名
  cursor.execute('''select product from productlist''')
  productname_info=cursor.fetchall()
  for i in productname_info:
    if i[0] not in productnamelist and i[0]!='':
      productnamelist.append(i[0])        #放进来的都是不重复的，非空的产品名字

  #提取出明日实验中的product name, test_category='Operation','Study'除外（other中）
  cursor.execute('''SELECT product,test_no,test_category,test_name FROM assignment WHERE 
  test_category!='Charger' and test_category!='Cable' and test_category!='Headset' and test_category!='LCM/TP' and 
  test_category!='Rear Cover/Inlay' and test_category!='PAD_Key_part' and test_category!='FPS' and test_category!='Film' and 
  test_category!='Housing' and test_category!='Protective Case' and test_category!='Label' and DATE=%s ''',tomorrowcolumn) 
  product_info=cursor.fetchall()

  #检查产品名是否 在列表中，
  for x in product_info:
    for strname in productnamelist:      
      if x[0] == strname: 
        templist0.append(x[0])       
      # if (x[0] == '' or x[0] is None) and (x[2]=='Study' or x[2]=='Operation') and '1' not in templist0: 
      #   print(x[0],x[2])
      #   templist0.append('1')  

    #针对Assignment中的每一条实验来说，去productnamelist中去找与之相同的名字，没有就输出一条错误；
    # 检查templist0中的元素数量，正常情况下应该只有一个元素
    if len(templist0)==1:  #列表中有一个元素才正确，多个或者没有都是错误的产品名                     
      templist0.clear() 
    elif (x[0] == '' or x[0] is None) and (x[2]=='Study' or x[2]=='Operation'): 
      templist0.clear() 
    else:          
      error1.append(' *STOP:%s,%s,%s, %s -->产品名错误! '%(str(x[1]),str(x[2]),str(x[3]),str(x[0]))) 
      templist0.clear()  

  #验证实验名称
  cursor.execute('''select test_name,test_category,vintage from test_name_list''')
  testname_info=cursor.fetchall()
  for i in testname_info:
    if i[0] not in testnamelist and i[0]!='':
      testnamelist.append(i[0])        #放进来的都是不重复的，非空的testname名字
    if i[1] not in category_list and i[1] is not None:
      category_list.append(i[1])
    if i[2] not in vintage_list and i[2] is not None:
      vintage_list.append(i[2])
  # print(category_list)
  # print(vintage_list)

  #提取出明日实验中的test name
  cursor.execute('''SELECT Test_no, product,test_category,test_name FROM assignment WHERE DATE=%s ''',tomorrowcolumn) 
  testname_info=cursor.fetchall()

  #检查产品名是否 在列表中，
  for x in testname_info:
    for strname in testnamelist:      
      if x[3] == strname: 
        templist0.append(x[1])       
    
    #检查templist0中的元素数量，正常情况下应该只有一个元素
    if len(templist0)==1:  #列表中有一个元素才正确，多个或者没有都是错误的产品名                     
      templist0.clear() 
    else:          
      error1.append(' *STOP:%s, %s ,%s, %s -->实验名称错误! '%(str(x[1]),str(x[0]),str(x[2]),str(x[3]))) 
      templist0.clear()  

  #提取出明日实验中的vintage, category, 可以为空，但是不能瞎写。test_name!=补工时，test_categroy!= Study/operation
  cursor.execute('''SELECT Test_no, product,vintage,test_category,test_name FROM assignment WHERE DATE=%s and test_name!='补工时' and test_category!='Study' and test_category!='Operation' ''',tomorrowcolumn) 
  test_info=cursor.fetchall()
 
  for x in test_info:
    if x[2] is not None and x[2] not in vintage_list: 
      print(x[2],'Vintage名称错误! ')   
      error1.append(' *STOP:%s, %s ,%s, %s --> Vintage名称错误! '%(str(x[1]),str(x[0]),str(x[2]),str(x[3]))) 
    if x[3] is not None and x[3] not in category_list:    
      print(x[3],'Category名称错误!')
      error1.append(' *STOP:%s, %s ,%s, %s --> Category名称错误! '%(str(x[1]),str(x[0]),str(x[2]),str(x[3])))  



   
    
  print('B-20：产品名及实验名称正确性检查完毕！')





      
  if error1:
    for e in error1:
      print(e)
    

  # sys.exit() 不在这里终止，在界面输出时终止


  cursor.close()
  conn.close()
  print('--------------------------------------------------------------------')
  return (new_test_upload,error,error1)

def test_distribe( ):  
  tomorrowcolumn=(datetime.datetime.now()+datetime.timedelta(days=1)).strftime('%Y-%m-%d') #明天年月日
  now=time.strftime('%H:%M:%S')
 

  db = pymysql.connect(host=ip_address,user='labuser',db='exceltodb', password='123456',port=3306,charset='utf8')    # port是数字，其它都是字符串          
  cursor = db.cursor()
################################################1月1日更新 条件中去掉Special !='cy', cy的也分派, 20210629又恢复，cy的不分########################################
  print('C-0: 开始实验分派。连接数据库成功。')
#第1次分派：Priority=1: 分ESD,Edu06。把日期为明日，speicial（不等于y，不等于s，不等于cy), operator='', esd&edu03的实验,根据esd_hours最小进行派发。派发后更新workload in namelist。    
  critical_test1 = 'ESD' 
  critical_test2 = 'EDu03'
  critical_test3 = 'EDu06'      
  cursor.execute('''select * from assignment where date=%s and Special !='y' and Special !='s' and Special !='cy'
  and (test_name like %s or test_name like %s or test_name like %s) and test_hour*coefficient>=4 and Priority = 1 
  and (Operator='' or Operator is null) and test_hour is not null ''',(tomorrowcolumn,('%'+critical_test1+'%'),('%'+critical_test2+'%'),('%'+critical_test3+'%'))) 
  pendingrows=cursor.fetchall()

  for row in pendingrows:    
    print('第1次分派选出实验：',row[0],row[1],row[2],row[6],row[7],float(row[8])*float(row[9]),row[10],row[11],row[14],row[16])

  #g记录分派实验个数
  g=0
  for row in pendingrows:       
     
    cursor.execute('''update assignment set Operator=(select c.employee_name from capability as c 
    inner join namelist as n on c.Employee_id=n.Employee_id where test_name like %s and Workload+%s*%s<=Estimatedhour1 and Workload <= 8
    order by n.esd_hours, n.Workload limit 1)where item=%s ''',(('%'+row[6]+'%'),row[8],row[9],row[0]))
    db.commit()
    #每一行实验派发后，，马上取出该行，更新该人的workload in namelist。
    cursor.execute('select * from assignment where item=%s',row[0])
    newrow0=cursor.fetchone()
    if newrow0[11] is not None:
      print('第1次分派结果:',row[1],row[2],row[6],row[7],row[10],newrow0[11],row[16])
      cursor.execute('''update namelist set workload=(select sum(test_hour*Coefficient) from assignment where 
      Date=(select max(date)from assignment)and Operator=%s) where Employee_name=%s''',(newrow0[11],newrow0[11]))
      db.commit()    
      g=g+1

    #check point
  print('C-1-第1次分派完成：首先针对Priority=1:ESD&Edu03&Edu06(工时>=4)的实验进行分派。共',g,'个实验。优先分给ESD_hours较少的人。')
######################################################################################
#第2次分派：Priority=1: 分运营商跌落和滚筒。把日期为明日，speicial（不等于y，不等于s，不等于cy), operator=''。派发后更新workload in namelist。    
  critical_test1 = '6533' 
  critical_test2 = '6534'
  critical_test3 = 'Tumble' 
  critical_test4 = 'ATT11'      
  cursor.execute('''select * from assignment where date=%s and Special !='y' and Special !='s' and Special !='cy' 
  and (test_name like %s or test_name like %s or test_name like %s or test_name like %s) and test_hour*coefficient>=4 and Priority = 1 
  and (Operator='' or Operator is null) and test_hour is not null ''',(tomorrowcolumn,('%'+critical_test1+'%'),('%'+critical_test2+'%'),('%'+critical_test3+'%'),('%'+critical_test4+'%'))) 
  pendingrows=cursor.fetchall()
  for row in pendingrows:
    print('第2次分派选出实验：',row[0],row[1],row[2],row[6],row[7],float(row[8])*float(row[9]),row[10],row[11],row[14],row[16])

  #g记录分派实验个数
  g=0
  for row in pendingrows:       
     
    cursor.execute('''update assignment set Operator=(select c.employee_name from capability as c 
    inner join namelist as n on c.Employee_id=n.Employee_id where test_name like %s and Workload+%s*%s<=Estimatedhour1 and Workload <= 8
    order by n.Workload limit 1)where item=%s ''',(('%'+row[6]+'%'),row[8],row[9],row[0]))
    db.commit()
    #每一行实验派发后，，马上取出该行，更新该人的workload in namelist。
    cursor.execute('select * from assignment where item=%s',row[0])
    newrow0=cursor.fetchone()
    if newrow0[11] is not None:
      print('第2次分派结果:',row[1],row[2],row[6],row[7],row[10],newrow0[11],row[16])
      cursor.execute('''update namelist set workload=(select sum(test_hour*Coefficient) from assignment where 
      Date=(select max(date)from assignment)and Operator=%s) where Employee_name=%s''',(newrow0[11],newrow0[11]))
      db.commit() 
      g=g+1

    #check point
  print('C-2-第2次分派完成：针对Priority=1:运营商跌落滚筒(>=4)的实验进行分派。共',g,'个实验。')
#第3次分派：Priority=1:分ALT。把日期为明日，speicial（不等于y，不等于s，不等于cy), operator='', ALT2015&2019的实验,根据alt_hours最小进行派发。派发后更新workload in namelist。    
  critical_test = '201'   # ALT2019,ALT2015
  cursor.execute('''select * from assignment where date=%s and Special !='y' and Special !='s' and Special !='cy'
  and test_name like %s and test_hour*coefficient>=4 and (Operator='' or Operator is null) and Priority = 1 and test_hour is not null''',(tomorrowcolumn,('%'+critical_test+'%'))) 
  pendingrows=cursor.fetchall()
  for row in pendingrows:
    print('第3次分派选出实验：',row[0],row[1],row[2],row[6],row[7],float(row[8])*float(row[9]),row[10],row[11],row[14],row[16])

  g=0
  for row in pendingrows:        
    cursor.execute('''update assignment set Operator=(select c.employee_name from capability as c 
    inner join namelist as n on c.Employee_id=n.Employee_id where test_name like %s and Workload+%s*%s<=Estimatedhour1 and Workload <= 8
    order by n.alt_hours, n.Workload limit 1)where item=%s ''',(('%'+row[6]+'%'),row[8],row[9],row[0]))
    db.commit()
    #每一行实验派发后，，马上取出该行，更新该人的workload in namelist。
    cursor.execute('select * from assignment where item=%s',row[0])
    newrow=cursor.fetchone()
    if newrow[11] is not None:
      print('第3次分派结果:',row[1],row[2],row[6],row[7],row[10],newrow[11],row[16])

      cursor.execute('''update namelist set workload=(select sum(test_hour*Coefficient) from assignment where 
      Date=(select max(date)from assignment)and Operator=%s) where Employee_name=%s''',(newrow[11],newrow[11]))
      db.commit()
     
      g=g+1


    #check point
  print('C-3-第3次分派完成：针对Priority=1:ALT(>=4)进行分派。共',g,'个实验。优先分给ALT_hours较少的人。')

  
##############################################################################################
#:第4次分派：把日期为明日，工时>=4,speicial（不等于y，不等于s), operator='',Priority=1 的实验进行派发。派发后更新workload in namelist。    
  cursor.execute('''select * from assignment where date=%s and Special !='y' and Special !='s' and Special !='cy'
  and priority=1 and test_hour*coefficient>=4 and (Operator='' or Operator is null) and test_hour is not null''',(tomorrowcolumn)) 
  pendingrows=cursor.fetchall()
  for row in pendingrows:
    print('第4次分派选出实验：',row[0],row[1],row[2],row[6],row[7],float(row[8])*float(row[9]),row[10],row[11],row[14],row[16])
  g=0
  for row in pendingrows:        
    cursor.execute('''update assignment set Operator=(select c.employee_name from capability as c 
    inner join namelist as n on c.Employee_id=n.Employee_id where test_name like %s and Workload+%s*%s<=Estimatedhour1 
    order by n.Workload, n.Employee_id limit 1)where item=%s ''',(('%'+row[6]+'%'),row[8],row[9],row[0]))
    db.commit()
    #每一行实验派发后，，马上取出该行，更新该人的workload in namelist。
    cursor.execute('select * from assignment where item=%s',row[0])
    newrow=cursor.fetchone()
    if newrow[11] is not None:
      print('第4次分派结果:',row[1],row[2],row[6],row[7],row[10],newrow[11],row[16])

      cursor.execute('''update namelist set workload=(select sum(test_hour*Coefficient) from assignment where 
      Date=(select max(date)from assignment)and Operator=%s) where Employee_name=%s''',(newrow[11],newrow[11]))
      db.commit()
      
      g=g+1
    #check point
  print('C-4-第4次分派完成：针对Priority=1 工时>=4 的所有实验进行分派。共',g,'个实验。')
  
  
#:第5次分派：把日期为明日，speicial（不等于y，不等于s), operator='',Priority=1 的所有实验进行派发。派发后更新workload in namelist。    
  cursor.execute('''select * from assignment where date=%s and Special !='y' and Special !='s' and Special !='cy'
  and priority=1 and (Operator='' or Operator is null) and test_hour is not null''',(tomorrowcolumn)) 
  pendingrows=cursor.fetchall()
  for row in pendingrows:
    print('第5次分派选出实验：',row[0],row[1],row[2],row[6],row[7],float(row[8])*float(row[9]),row[10],row[11],row[14],row[16])
  g=0
  for row in pendingrows:        
    cursor.execute('''update assignment set Operator=(select c.employee_name from capability as c 
    inner join namelist as n on c.Employee_id=n.Employee_id where test_name like %s and Workload+%s*%s<=Estimatedhour1 
    order by n.Workload, n.Employee_id limit 1)where item=%s ''',(('%'+row[6]+'%'),row[8],row[9],row[0]))
    db.commit()
    #每一行实验派发后，，马上取出该行，更新该人的workload in namelist。
    
    cursor.execute('select * from assignment where item=%s',row[0])
    newrow=cursor.fetchone()
    if newrow[11] is not None:
      print('第5次分派结果:',row[1],row[2],row[6],row[7],row[10],newrow[11],row[16])
    

      cursor.execute('''update namelist set workload=(select sum(test_hour*Coefficient) from assignment where 
      Date=(select max(date)from assignment)and Operator=%s) where Employee_name=%s''',(newrow[11],newrow[11]))
      db.commit()
      
      g=g+1
    #check point
  print('C-5-第5次分派完成：针对Priority=1 的所有实验进行分派。共',g,'个实验。')  
  
   #暂停点
  # print("按'回车键'或'空格键'继续......")        
  # while True: 
  #   char=ord(msvcrt.getch()) 
  #   if char==13 or char==32:         
  #     break

  

#:第6 次分派：把日期为明日，speicial（不等于y，不等于s), operator='',Priority=2 的实验进行派发。派发后更新workload in namelist。    
  critical_test1 = 'ESD' 
  critical_test2 = 'Edu03'      
  cursor.execute('''select * from assignment where date=%s and Special !='y' and Special !='s' and Special !='cy' and 
  (test_name like %s or test_name like %s) and Priority = 2 and (Operator='' or Operator is null) and test_hour is not null ''',(tomorrowcolumn,('%'+critical_test1+'%'),('%'+critical_test2+'%'))) 
  pendingrows=cursor.fetchall()
  for row in pendingrows:
    print('第6次分派选出实验：',row[0],row[1],row[2],row[6],row[7],float(row[8])*float(row[9]),row[10],row[11],row[14],row[16])
  g=0
  for row in pendingrows:    
    cursor.execute('''update assignment set Operator=(select c.employee_name from capability as c 
    inner join namelist as n on c.Employee_id=n.Employee_id where test_name like %s and Workload+%s*%s<=Estimatedhour1
    order by n.esd_hours, n.Workload limit 1)where item=%s ''',(('%'+row[6]+'%'),row[8],row[9],row[0]))
    db.commit()
    #每一行实验派发后，，马上取出该行，更新该人的workload in namelist。
    cursor.execute('select * from assignment where item=%s',row[0])
    newrow=cursor.fetchone()
    if newrow[11] is not None:
      print('第6次分派结果:',row[1],row[2],row[6],row[7],row[10],newrow[11],row[16])

      cursor.execute('''update namelist set workload=(select sum(test_hour*Coefficient) from assignment where 
      Date=(select max(date)from assignment)and Operator=%s) where Employee_name=%s''',(newrow[11],newrow[11]))
      db.commit()

      g=g+1
    #check point
  print('C-6-第6次分派完成：针对Priority=2 ESD 实验进行分派，优先给ESD-hour少的人。共',g,'个实验。')
   

#:第7 次分派：把日期为明日，speicial（不等于y，不等于s), operator='',Priority=2 的实验进行派发。派发后更新workload in namelist。    
  critical_test = '201'   # ALT2019,ALT2015    
  cursor.execute('''select * from assignment where date=%s and Special !='y' and Special !='s' and Special !='cy'
  and test_name like %s and Priority = 2 and (Operator='' or Operator is null) and test_hour is not null ''',(tomorrowcolumn,('%'+critical_test+'%'))) 
  pendingrows=cursor.fetchall()
  for row in pendingrows:
    print('第7次分派选出实验：',row[0],row[1],row[2],row[6],row[7],float(row[8])*float(row[9]),row[10],row[11],row[14],row[16])
  g=0
  for row in pendingrows:    
    cursor.execute('''update assignment set Operator=(select c.employee_name from capability as c 
    inner join namelist as n on c.Employee_id=n.Employee_id where test_name like %s and Workload+%s*%s<=Estimatedhour1
    order by n.alt_hours, n.Workload limit 1)where item=%s ''',(('%'+row[6]+'%'),row[8],row[9],row[0]))
    db.commit()
    #每一行实验派发后，，马上取出该行，更新该人的workload in namelist。
    cursor.execute('select * from assignment where item=%s',row[0])
    newrow=cursor.fetchone()
    if newrow[11] is not None:
      print('第7次分派结果:',row[1],row[2],row[6],row[7],row[10],newrow[11],row[16])

      cursor.execute('''update namelist set workload=(select sum(test_hour*Coefficient) from assignment where 
      Date=(select max(date)from assignment)and Operator=%s) where Employee_name=%s''',(newrow[11],newrow[11]))
      db.commit()

      g=g+1
    #check point
  print('C-7-第7次分派完成：针对Priority=2 ALT 实验进行分派，优先给ALT-hours少的人。共',g,'个实验。')
 
  cursor.execute('''select * from assignment where date=%s and Special !='y' and Special !='s' and Special !='cy'
  and priority=2 and (Operator='' or Operator is null) and test_hour is not null''',(tomorrowcolumn)) 
  
  pendingrows=cursor.fetchall()
  for row in pendingrows:
    print('第8次分派选出实验：',row[0],row[1],row[2],row[6],row[7],float(row[8])*float(row[9]),row[10],row[11],row[14],row[16])
  g=0
  for row in pendingrows:    
    cursor.execute('''update assignment set Operator=(select c.employee_name from capability as c 
    inner join namelist as n on c.Employee_id=n.Employee_id where test_name like %s and Workload+%s*%s<=Estimatedhour1
     order by n.Workload, n.Employee_id limit 1)where item=%s ''',(('%'+row[6]+'%'),row[8],row[9],row[0]))
    db.commit()
    #每一行实验派发后，，马上取出该行，更新该人的workload in namelist。
    cursor.execute('select * from assignment where item=%s',row[0])
    newrow=cursor.fetchone()
    if newrow[11] is not None:
      print('第8次分派结果:',row[1],row[2],row[6],row[7],row[10],newrow[11],row[16])

      cursor.execute('''update namelist set workload=(select sum(test_hour*Coefficient) from assignment where 
      Date=(select max(date)from assignment)and Operator=%s) where Employee_name=%s''',(newrow[11],newrow[11]))
      db.commit()

      g=g+1
    #check point
  print('C-8-第8次分派完成：针对Priority=2 的所有实验进行分派。共',g,'个实验。')
    # 暂停点
  # print("按'回车键'或'空格键'继续......")        
  # while True: 
  #   char=ord(msvcrt.getch()) 
  #   if char==13 or char==32:         
  #     break

#:第9次分派：把日期为明日，speicial（不等于y，不等于s),operator='',Priority=3 工时>=4的实验进行派发。派发后更新workload in namelist。

  cursor.execute('''select * from assignment where date=%s and Special !='y' and Special !='s' and Special !='cy' and test_hour*coefficient>=4 
  and priority=3 and (Operator='' or Operator is null) and test_hour is not null''',(tomorrowcolumn)) 
  
  pendingrows=cursor.fetchall()
  for row in pendingrows:
    print('第9次分派选出实验：',row[0],row[1],row[2],row[6],row[7],float(row[8])*float(row[9]),row[10],row[11],row[14],row[16])
  g=0
  for row in pendingrows:    
    cursor.execute('''update assignment set Operator=(select c.employee_name from capability as c 
    inner join namelist as n on c.Employee_id=n.Employee_id where test_name like %s and Workload+%s*%s<=Estimatedhour1
    order by n.Workload, n.Employee_id limit 1)where item=%s ''',(('%'+row[6]+'%'),row[8],row[9],row[0]))
    db.commit()
    #每一行实验派发后，，马上取出该行，更新该人的workload in namelist。
    cursor.execute('select * from assignment where item=%s',row[0])
    newrow=cursor.fetchone()
    if newrow[11] is not None:
      print('第9次分派结果:',row[1],row[2],row[6],row[7],row[10],newrow[11],row[16])

      cursor.execute('''update namelist set workload=(select sum(test_hour*Coefficient) from assignment where 
      Date=(select max(date)from assignment)and Operator=%s) where Employee_name=%s''',(newrow[11],newrow[11]))
      db.commit()   

      g=g+1
    #check point
  print('C-9-第9次分派完成：针对Priority=3 工时>=4实验分派完成。共',g,'个实验。')

#:第10次分派：把日期为明日，speicial（不等于y，不等于s),operator='',Priority=3 工时>=2的实验进行派发。派发后更新workload in namelist。

  cursor.execute('''select * from assignment where date=%s and Special !='y' and Special !='s' and Special !='cy' and test_hour*coefficient>=2 
  and priority=3 and (Operator='' or Operator is null) and test_hour is not null''',(tomorrowcolumn)) 
  
  pendingrows=cursor.fetchall()
  for row in pendingrows:
    print('第10次分派选出实验：',row[0],row[1],row[2],row[6],row[7],float(row[8])*float(row[9]),row[10],row[11],row[14],row[16])
  g=0
  for row in pendingrows:    
    cursor.execute('''update assignment set Operator=(select c.employee_name from capability as c 
    inner join namelist as n on c.Employee_id=n.Employee_id where test_name like %s and Workload+%s*%s<=Estimatedhour1
    order by n.Workload, n.Employee_id limit 1)where item=%s ''',(('%'+row[6]+'%'),row[8],row[9],row[0]))
    db.commit()
    #每一行实验派发后，，马上取出该行，更新该人的workload in namelist。
    cursor.execute('select * from assignment where item=%s',row[0])
    newrow=cursor.fetchone()
    if newrow[11] is not None:
      print('第10次分派结果:',row[1],row[2],row[6],row[7],row[10],newrow[11],row[16])

      cursor.execute('''update namelist set workload=(select sum(test_hour*Coefficient) from assignment where 
      Date=(select max(date)from assignment)and Operator=%s) where Employee_name=%s''',(newrow[11],newrow[11]))
      db.commit()   

      g=g+1
    #check point
  print('C-10-第10次分派完成：针对Priority=3 工时>=2实验分派完成。共',g,'个实验。')
     #暂停点
  # print("按'回车键'或'空格键'继续......")        
  # while True: 
  #   char=ord(msvcrt.getch()) 
  #   if char==13 or char==32:         
  #     break

#:第11次分派：把日期为明日，speicial（不等于y，不等于s),operator='',Priority=3 所有实验进行派发。派发后更新workload in namelist。

  cursor.execute('''select * from assignment where date=%s and Special !='y' and Special !='s' and Special !='cy'
  and priority=3 and (Operator='' or Operator is null) and test_hour is not null ''',(tomorrowcolumn)) 
  
  pendingrows=cursor.fetchall()
  for row in pendingrows:
    print('第11次分派选出实验：',row[0],row[1],row[2],row[6],row[7],float(row[8])*float(row[9]),row[10],row[11],row[14],row[16])
  g=0
  for row in pendingrows:    
    cursor.execute('''update assignment set Operator=(select c.employee_name from capability as c 
    inner join namelist as n on c.Employee_id=n.Employee_id where test_name like %s and Workload+%s*%s<=Estimatedhour1
    order by n.Workload, n.Employee_id limit 1)where item=%s ''',(('%'+row[6]+'%'),row[8],row[9],row[0]))
    db.commit()
    #每一行实验派发后，，马上取出该行，更新该人的workload in namelist。
    cursor.execute('select * from assignment where item=%s',row[0])
    newrow=cursor.fetchone()
    if newrow[11] is not None:
      print('第11次分派结果:',row[1],row[2],row[6],row[7],row[10],newrow[11],row[16])

      cursor.execute('''update namelist set workload=(select sum(test_hour*Coefficient) from assignment where 
      Date=(select max(date)from assignment)and Operator=%s) where Employee_name=%s''',(newrow[11],newrow[11]))
      db.commit()   

      g=g+1
    #check point
  print('C-11-第11次分派完成：针对Priority=3 所有实验分派完成。共',g,'个实验。')
  print('C-12：总共11分派全部完成！！！')
  # 暂停点
  # print("按'回车键'或'空格键'继续......")        
  # while True: 
  #   char=ord(msvcrt.getch()) 
  #   if char==13 or char==32:         
  #     break

 
    
   #namelist中标注在岗
  cursor.execute(''' update namelist set OT='在岗' where Estimatedhour1>=1''') 
  #namelist中标注夜班
  cursor.execute(''' update namelist set OT='夜班' where Estimatedhour1>0 and Estimatedhour1<0.5''') 
  #namelist中标注休假
  cursor.execute(''' update namelist set OT='--休假--' where Estimatedhour1=0''') 
  #namelist中设置OT建议
  cursor.execute(''' update namelist set OT='! 加 班 ！' where Estimatedhour1>8 and workload>8 and workload<=10.5''') 
  #namelist中超限警示
  cursor.execute(''' update namelist set OT='~异常*超限~' where workload>Estimatedhour1''') 

  #Spare_hour列设置
  cursor.execute(''' update namelist set Spare_hours = Estimatedhour1 - Workload where Estimatedhour1>7 and Estimatedhour1>=Workload ''')   
  cursor.execute('update namelist set Time=%s',now)
  db.commit()

  #更新namelist_copy sheet
  cursor.execute('delete from namelist_copy where Date=%s',tomorrowcolumn)
  cursor.execute('insert into namelist_copy select * from namelist') 
  db.commit()

    #check point
  print('C-13：删除namelist_copy where date=明日，把namelist中的数据插入进来！')

  #更新实验预计完成日期ComDate（包含了对a实验的更新：只要Operator中有人，且日期为今日或明日的都更新（加a 且不延续的实验，date会是今日）
  cursor.execute('''update assignment set ComDate = DATE_ADD(StartDate,INTERVAL Total_day DAY) where Operator is not null and Operator!='' and (date=%s or date=%s)''',(todaycolumn,tomorrowcolumn)) 
  db.commit()
  # Special='s'的实验， 完成时间+1
  cursor.execute('''update assignment set ComDate = DATE_ADD(ComDate,INTERVAL 1 DAY) where Special = 's' and Operator='' and date=%s''',tomorrowcolumn) 
  db.commit()

  #.分活儿结果汇总：
  #cursor.execute('select count(*) from assignment where Operator is not null and date=%s',(tomorrowcolumn))
  #x=cursor.fetchone()
  cursor.execute('''select count(*) from assignment where Operator!='' and Operator is not null and Special!='s' and date=%s''',(tomorrowcolumn))
  y=cursor.fetchone()
  cursor.execute('''select count(*) from namelist where OT='! 加 班 ！' ''')
  z=cursor.fetchone()
  cursor.execute('''select (sum(test_hour*Coefficient)+1) from assignment where Special!='s' and Operator!='' and Operator is not null and date=%s''',(tomorrowcolumn)) #明日总工时
  p=cursor.fetchone()
  cursor.execute('''select (sum(test_hour*Coefficient)+1) from assignment where Operator!='' and Operator is not null and date=%s''',(todaycolumn)) #今日总工时
  p_today=cursor.fetchone()

  cursor.execute('select count(estimatedhour1) from namelist where estimatedhour1 >0 ')  #明日在岗人数包括了夜班
  q=cursor.fetchone()
  ondutyhours =(q[0]-1)*8   #夜班1人算0.5人，另0.5人忽略，不加班状态8小时每天的总工作量
  if ondutyhours==0:  #解决当3人时下面分母为0报错问题
    ondutyhours=1
 
  cursor.execute('''select count(estimatedhour1) from namelist_copy where estimatedhour1 >0 and date=%s''',todaycolumn)  #今日在岗人数包括了夜班
  q_today=cursor.fetchone()   
  if q_today[0]!=0 and q_today[0]!=1:
    ondutyhours=(q_today[0]-1)*8   #夜班2人算1人，1人就算0，不加班状态8小时每天的总工作量
  else:
    ondutyhours= 160

  x=1
  if p[0] is not None:
    x=p[0]
  else:
    x=1
# 今日总工时
  if p_today[0] is not None:
    x_today=int(p_today[0])
  else:
    x_today=1

  percent=int((x_today/ondutyhours)*100)
  # print('今日工时、负荷量：',x_today,percent)

  #20240418新增：把今日工时和负荷量写入数据库表格workload_trend中，在画待分工作量图时使用
  cursor.execute('''update workload_trend set todayload=%s, loadpercent=%s where year_date=%s''',(x_today,percent,todaycolumn))
  db.commit()
   

  #5. 取完数据要断开数据库，释放资源
  cursor.close()
  db.close()
  print('C-14:分活儿完成。汇总如下：')
  distribution_result=(' {}个实验已分派(包括延续和新实验), 今日工时{:.1f},明日工时{:.1f}。'.format(y[0],x_today,x)) 
  percentage=(' 明天在岗{}人,今日实验负荷量约为{:.1f}%! '.format(q[0],percent))    
  print(distribution_result) 
  print(percentage)
  print('*说明1：今日工时汇总的是今日正在进行的已分配的所有实验的总工时，包括今天加a的，不包括暂停的')
  print('*说明2：今日实验负荷量计算方法为：今日工时/(今日在岗人数-1)*8Hours, 在岗人数包括夜班,-1是对夜班人数计算的调整。')
  return(distribution_result,percentage)





#---------先生成图片，显示，保存，再附在邮件中发送出去-----------
def auto_email(text):  
  todaycolumn=datetime.datetime.now().strftime('%Y-%m-%d')        
  tomorrowcolumn=(datetime.datetime.now()+datetime.timedelta(days=1)).strftime('%Y-%m-%d')  
  todaycolumn=datetime.datetime.now().strftime('%Y-%m-%d')   
  
  #连接数据库
  db = pymysql.connect(host=ip_address,user='labuser',db='exceltodb',
          password='123456',port=3306,charset='utf8')
  # 获得游标
  cursor = db.cursor()
  print('E-1:开始准备邮件发送！')
  cursor.execute('''select name from special_position''')
  names=cursor.fetchall()
  special_names=[]
  for i in names:
    special_names.append(i[0])
 




  def workload_next(ip_address, x_days):  #x_days=1 代表明日开始的工时，2 代表今天，3代表昨天，4代表前天
 
    tomorrowcolumn=(datetime.datetime.now()+datetime.timedelta(days=1)).strftime('%Y-%m-%d') 
    conn = pymysql.connect(host=ip_address,user='labuser',db='exceltodb',
            password='123456',port=3306,charset='utf8')
    # 获得游标
    cursor = conn.cursor()
    hour_list =[]    
    todayloadlist=[]
    total_hours = 0  #每日的总工时
    date_list = []

    #当日如果重复分活儿，会先清空之前数据，应对导入实验增加的情况
    cursor.execute('''select max(year_date) from workload_trend''')
    max_date=cursor.fetchone()

    if max_date[0].strftime("%Y-%m-%d")==tomorrowcolumn:      
      cursor.execute('''delete from workload_trend where year_date=%s''',tomorrowcolumn)        
      conn.commit()


    for i in range(x_days,0,-1):      

    #获取日期,#定义查询天数  大小写不一样哦！
      pre_date=(datetime.datetime.now()+datetime.timedelta(days=2-i)).strftime('%Y%m%d') 
      pre_date_md=(datetime.datetime.now()+datetime.timedelta(days=2-i)).strftime('%m/%d')    # 取这个日期只为返回画图时不包含年
      # print(pre_date)
      #先取出DB中workload_trend中的Date,存成一个date_list_store:
      date_list_store=[]



      cursor.execute('''select date from workload_trend where year_date>=%s''',pre_date)
      lists=cursor.fetchall()
      for j in lists:
        date_list_store.append(j[0])
      
      # print(date_list_store)

      #date_list为画图用，不含年
      date_list.append(pre_date_md)

      templist = []  #临时存放每个实验的每日工时，从第一天到最后一天

      #如果该日期没有在workload_trend的表中，说明还没计算过
      if pre_date_md not in date_list_store:
        # print(pre_date_md)        
      
        cursor.execute('''select test_name, coefficient, Date, Left_day from inprocess where test_hour is not Null and Date=%s ''',pre_date)
        # 取出查取日的所有实验，包括名称，系数等。
        coming_tests=cursor.fetchall()
        #逐条实验查取每日的test_hour
        for test in coming_tests:
          print(test[0],test[1],test[2],test[3])
          cursor.execute('''select Test_hour from testlist where test_name like concat('%%',(%s),'%%' ) order by test_day''',test[0])
          hours_by_day = cursor.fetchall()
          #取出的每日test_hour 按照test_day顺序放入 templist
          for hours in hours_by_day:
              templist.append(hours[0])  #生成每日工时列表
          n = test[3]    #剩余天数
          #根据实验剩余天数来汇总剩余总工时
          test_hour = sum(templist[-n:]) *float(test[1])  #每个test的剩余工时（乘以了系数）
          total_hours = total_hours + test_hour   #当日所有实验的总工时
          # print(test[0],test[3],test_hour)  # test, left_day, left_hours
          templist.clear()   

        #每日实验总工时放入一个列表,如果工时太少则让它等于上一日的工时, 如果第一个就为0，<10，那就只能为0了
        if total_hours<10 and hour_list!=[]:
          total_hours = hour_list[-1]  
          # total_hours = 888  

        hour_list.append(int(total_hours)) 
        #执行明日日期时会添加一个None,否则列表元素数会少一个
        todayloadlist.append(None)

        #把workload和日期写入workload_trend表格

        query='''insert into workload_trend (workload,date,year_date) values(%s,%s,%s)'''
        workload=int(total_hours)
        date=pre_date_md
        year_date=pre_date

        values =(workload,date,year_date)      
        # 执行sql语句
        cursor.execute(query,values) 
        conn.commit() 

        #每日总工时清零，去放下一日的
        total_hours=0   

      else:
        cursor.execute('''select workload,todayload from workload_trend where date=%s and year_date=%s''',(pre_date_md,pre_date))
        date_get=cursor.fetchone()
        hour_list.append(date_get[0])
        todayloadlist.append(date_get[1])   

    return(hour_list,date_list,todayloadlist)
        
  # 第1个图-----近期工作量走势图(V2.0更新，每日待分总工时取自inprocess)------------------------------
  item = workload_next(ip_address,180)
  
  xdate=item[1]
  yload=item[0]
  todayload=item[2]

 
  ymaxcapacity=[]
  # ymincapacity=[]
  # #遍历表里的数据
  for x in xdate:
  #   #xname.append(x[0])
    ymaxcapacity.append(1250)
 
  plt.figure(figsize=(8,3))
  plt.plot()


  #设置坐标刻度字体大小
  plt.xticks(fontsize=5,rotation=45)
  plt.yticks(fontsize=8)
  plt.ylim(0,1850)  #y轴刻度最小到最大
  plt.yticks([0,250,500,750,1000,1250,1500,1750,2000,2250,2500,2750]) #y轴刻度，不加按默认
  
  plt.plot(xdate,ymaxcapacity,label="Capacity limit(~250hrs/day)",color="r",linewidth=0.5,linestyle="--")
  #plt.plot(xdate,ymincapacity,label="Min.Capacity",color="b",linewidth=0.3,linestyle="--")
  plt.plot(xdate,yload,label="Workload",color="b",linewidth=0.6)
 

  ax = plt.gca()
 
 
  ax.xaxis.set_major_locator(ticker.IndexLocator(3,5))   #根据给出的间隔和起始位置来设置

  plt.grid(True)
  plt.grid(color='g',linestyle='--',linewidth='0.1')#修改网格颜色，类型为虚线

  # plt.xlabel('Date',fontsize = 5)
  plt.ylabel('Hours',fontsize = 8)
  plt.title('待分实验工作量走势图(6个月)',fontsize = 8)
  
  #添加图例，prop指定图例的字体
  plt.legend(loc="lower left",fontsize='x-small')
  # plt.tight_layout(3)  #控制图间距离
  plt.subplots_adjust(left=None, bottom=0.15, right=None, top=0.90,wspace=None, hspace=0.4)
   # subplots(图左下角坐标（left,bottom)，右上角坐标(right, top)，图间s上下距(hspace)，整个图大小为1)
  #显示图表
  #plt.show()  #生产文件就不要展示了
  #plt.savefig('image_0.png',dpi = 600)
  #plt.close()  #每次存之后用这句清一下缓存，避免图重叠
  print('E-2：图1: 工作量走势图，Okay!')
  

  

  plt.savefig('image_1.png',dpi = 300)

  


# ---------获取人员在岗信息（白班）
  cursor.execute('select Employee_name from namelist where Estimatedhour1>0.1 and Employee_name not in %s and Date = %s', (special_names,tomorrowcolumn) ) 
  #将获取到的sql数据全部显示出来
  result0=cursor.fetchall()
  #定义需要用上的空数据数组，然后通过遍历数据库的数据将数据附上去
  name_dayshift =[]  
  #遍历表里的数据
  for x in result0:
    name_dayshift.append(x[0])
  #列表元素先变成str
  list_d = [str(i) for i in name_dayshift]  
  #把列表元素连接成字符串
  str_name_d = (',').join(list_d)
  print('E-10：获取明日在岗人员清单!')
# ----------获取人员在岗信息（夜班）
  cursor.execute('select Employee_name from namelist where Estimatedhour1=0.1 and Date = %s', tomorrowcolumn ) 
  #将获取到的sql数据全部显示出来
  result=cursor.fetchall()
  #定义需要用上的空数据数组，然后通过遍历数据库的数据将数据附上去
  name_nightshift =[]  
  #遍历表里的数据
  for x in result:
    name_nightshift.append(x[0])
  #列表元素先变成str
  list_n = [str(i) for i in name_nightshift]  
  #把列表元素连接成字符串
  str_name_n = (',').join(list_n)
  print('E-11：获取夜班在岗人员清单!')
# -----------获取人员休假信息 
  cursor.execute('''select Employee_name from namelist where Estimatedhour1=0 and Employee_name not in %s and Date = %s''', (special_names,tomorrowcolumn) ) 
  #将获取到的sql数据全部显示出来
  result1=cursor.fetchall()
  #定义需要用上的空数据数组，然后通过遍历数据库的数据将数据附上去
  name_offduty =[]  
  #遍历表里的数据
  for x in result1:
    name_offduty.append(x[0])
  #列表元素先变成str
  list_o = [str(i) for i in name_offduty]  
  #把列表元素连接成字符串
  str_name_o = (',').join(list_o)
  print('E-12：获取明日休假人员清单!')
 




  # 获取当前系统用户名
  user_name = getpass.getuser() 
  assign_time=datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')  
  #释放游标，断开数据库，释放资源
  cursor.execute('''insert into test_assignment_record (user_name,assign_time,sw_version) values (%s,%s,%s)''', (user_name,assign_time,version))
  db.commit()


  cursor.close()
  db.close()



  print('E-19：分活儿汇总图形已全部完成，分活儿人： --> %s' % user_name)

  #----------------邮件发送--------------------------------------
  from_addr='tcc_reliability@motorola.com'
  #加一个时间判断来决定邮件收件人包括谁，周一12点后发给更多人
  today = datetime.datetime.now()
  # if today.weekday()==0 and today.hour>=12:
  #   to_addr=['lijun24@lenovo.com','nizhao2@lenovo.com','wangjin24@lenovo.com','pengch2@lenovo.com','zhangyu90@lenovo.com','tanpl1@lenovo.com',
  #   'wanghuan32@lenovo.com','liaoqh1@lenovo.com','chenkai12@lenovo.com','zhaojk2@lenovo.com','xiongcq2@lenovo.com','zhangyq14@lenovo.com','zhangcx10@lenovo.com',
  #   'dongsheng4@lenovo.com','lizh48@lenovo.com','xuhang3@lenovo.com','hongxuan3@lenovo.com','weiyx6@lenovo.com','xiongwj2@lenovo.com']  
  # else:
  # to_addr=['lijun24@lenovo.com']  
  to_addr=['lijun24@lenovo.com','nizhao2@lenovo.com','wangjin24@lenovo.com','pengch2@lenovo.com','zhangyu90@lenovo.com','tanpl1@lenovo.com'] 
  # 构建message
  msg = MIMEMultipart()

  msg['From'] = Header(from_addr)  
  msg['To'] = Header(",".join(to_addr))  

  if ip_address=='10.114.183.17':
    header_name ='Automatic_Test_Assignment '+version+' '+releasedate+' Run by:'+user_name
  else:
    header_name ='Automatic_Test_Assignment_ * 分活儿软件测试，请忽略本邮件内容！* '
  msg['Subject'] = Header(header_name,'utf-8') #utf-8可以不加


  # 以html格式构建邮件内容
  send_str = '<html><body>'
  send_str += '<left>'+'*<U>分活儿结果：</U>'+text+'</left>'   
  send_str += '<center> <br></center>'
  send_str += '<center> </center>'
  send_str += '<left>'+ '*<U>明日白班：</U>'+ str_name_d +'</left>'
  send_str += '<center> </center>'   
  send_str += '<left>' +'*<U>明日夜班：</U>' + str_name_n +'</left>'  
  send_str += '<center> </center>' 
  send_str += '<left>'+ '*<U>明日休假：</U>' + str_name_o +'</left>'    
  send_str += '<center> <br></center>'
  # send_str += '<left>'+ '*<U>明日主要实验:</U>'+'<br></left>'  
  # send_str += '<left>'+ str_name_t +'</left>'  
  # send_str += '<center> <br></center>'
  # send_str += '<left>'+ '*<U>关键实验排队状况:</U>'+'<br></left>'  
  # send_str += '<left>'+ str_name_c +'<br></left>'  
  # send_str += '<left>'+ str_name_c1 +'</left>' 
  # 下面这个方法可以变换字体及颜色
  # send_str += '<left>'+ '<font size="3" color="red">'+str_name_t+'</font>' +'</left>'  
  send_str += '<center> <br></center>'  #有这行导致两图之间间距过大
 
  send_str += '<img src="cid:image1" alt="image1" align="center" width=100% >'
  send_str += '<center> <br></center>'  #有这行导致两图之间间距过大
  send_str += '<center> <br></center>'  #有这行导致两图之间间距过大

  # send_str += '<center> </center>'  #有这行导致两图之间间距过大
  # send_str += '<img src="cid:image2" alt="image2" align="center" width=100% >'
  send_str += '<center> </center>'
  time_emailout = datetime.datetime.now() #程序计时  
  total_time=(time_emailout - time_start).seconds+5
  send_str += '<center>本邮件内容基于当天最后一次的分活儿结果。Server: '+ip_address+'  '+version+' '+releasedate+' 分活儿时长：'+str(total_time)+'秒</center>'
  send_str += '</body></html>'

  # 添加邮件内容
  content = MIMEText(send_str, _subtype='html', _charset='utf8')
  msg.attach(content)
  # 构建并添加图像对象
  #img0 = MIMEImage(open('image_0.png', 'rb').read(), _subtype='octet-stream')
  #img0.add_header('Content-ID', 'image0')
  #msg.attach(img0)

  img1 = MIMEImage(open('image_1.png', 'rb').read(), _subtype='octet-stream')
  img1.add_header('Content-ID', 'image1')
  msg.attach(img1)

  # img2 = MIMEImage(open('image_2.png', 'rb').read(), _subtype='octet-stream')
  # img2.add_header('Content-ID', 'image2')
  # msg.attach(img2)
  print('E-20: 邮件发送准备工作已完成！')
  #发送邮件
  error9=[]
  # API 地址
  api_url = 'http://100.64.5.171/WebServices/WS_EMAIL/WS_EMAIL.asmx?wsdl'
  email_params = {
      'message':  msg.as_string()  # 将邮件内容转换为字符串
  }

  # 发送邮件
  try:
      client = Client(api_url)
      response = client.service.sendEmail(**email_params)
      msg=(" 邮件发送成功！分活儿完成！")
  except Exception as e:
      msg=("*** 邮件发送失败!", e)
      error9.append(msg)
      

  print(msg)  
 
  time.sleep(5)
  os.remove('image_1.png')  #用完删除图片
  # os.remove('image_2.png')  #用完删除图片
  plt.close('all')  #清内存

  return(msg,error9)


#分活儿后的实验查询功能：列出会该实验的，workload 最少的5人。
def test_query(query):
    db = pymysql.connect(host=ip_address,user='labuser',db='exceltodb',
            password='123456',port=3306,charset='utf8')    # port是数字，其它都是字符串          
    cursor = db.cursor()

     # 根据query查询该实验周期和首日工时，20220210update limit1  限制查询出的是第一个实验
    cursor.execute('''select Left_day,Test_hour,test_name from testlist where test_day='1'and test_name like %s limit 1 ''',('%'+query+'%')) 
    test_info=cursor.fetchone()
 
    # 20220210修改：根据query查询出第一个实验，再基于该实验查询会做该实验的所有人，去掉了workload<Estimatedhour-1的条件，按工时从小到大排序
    cursor.execute('''select c.employee_name,n.workload from capability as c inner join namelist as n on 
    c.Employee_id=n.Employee_id where test_name=%s order by n.Workload, n.Employee_id limit 20 ''',test_info[2])
    pendingrows=cursor.fetchall()
    query_result=[str(test_info[2])+':该实验首日工时：'+str(test_info[1])+'小时,'+'周期'+str(test_info[0])+'天。'+'\n']
    for row in pendingrows:
        query_result.append('*.'+str(row[0])+'：当前workload为：'+str(row[1])+'小时。')
    db.commit()

      #7.释放游标，断开数据库，释放资源
    cursor.close()
    db.close()

    return query_result

#ESD query功能：
def esd_query():

  return(['停用！！！'])


#用户界面
def windows():  
    window=tk.Tk()
    title_name='TCC Test Assignment '+version+' '+releasedate
    window.title(title_name)
    window.geometry('550x502')
    
    tmp=open('icon.gif','wb')
    tmp.write(base64.b64decode(icon))
    tmp.close()

    canvas=tk.Canvas(window,height=820,width=800)
    image_file=tk.PhotoImage(file='icon.gif')
    image=canvas.create_image(40,10,anchor='nw',image=image_file)
    canvas.pack(side='top')
    
    #实验查询输入框
    E1 = tk.Entry(window,bd =4)
    E1.place(x=198,y=465)

    # 标签与列表框：
    # tk.Label(window,text='执行报告:',font=('Arial Black',10),width=15,height=2).place(x=30,y=210)

    var=tk.StringVar()
    lb=tk.Listbox(window,listvariable=var,font=('Fangsong',10),width=67,height=16) 

    lb.place(x=43,y=228,anchor='nw')

    #执行分活儿
    def sw_proceed():
      # question=tk.messagebox.askokcancel(title='Warning',message='练习版使用时Test plan要使用昨日分活儿之后的表格，不能使用已完成今日正式分活儿之后的表格。练习版使用备份数据库，该数据库会在每晚11点与正式数据库进行同步，因此练习时产生的数据每晚都会被删除。使用前请先在C盘建一个自动分活儿文件夹并在桌面生成同名快捷方式，并复制TCC分活儿表及每日工时核对表格到该文件夹中。')
      question=tk.messagebox.askokcancel(title='Warning',message='请确保明日在岗状态的正确更新，否则会影响分活儿结果及工时统计!')
      if question:      
        global time_start
        time_start = datetime.datetime.now() #程序计时起点  
        lb.delete(0,'end') 
        lb.insert(0,'')     
        startline=' 连接服务器:'+ip_address+', 执行Test Plan的备份/清理/恢复，请稍候！......'     
        lb.insert(1,startline)  
        window.update()
        item0 = sheet_recover.plan_sheet_recover(ip_address)
        lb.delete(1)        

        if item0[0][0]=='恢':
          lb.insert(1,item0[0]) 
          lb.itemconfig(1,fg="red")
          window.update()
        else:
          lb.insert(1,item0[0])  
        # lb.itemconfig(1,fg="blue")
        # lb.insert(1,item0[0][0]) 
        # if item0[1]:
        #   lb.insert(1,item0[1][0]) 
          window.update()
          time.sleep(1)
          lb.insert(2,'') 
          lb.insert(3,' 开始导入正在进行的今日实验数据到数据库......')  
          window.update()

          item1=old_upload()
          lb.delete(3)

          window.update()  
          if item1[0]=='导':    
            lb.insert(3,item1)  
            lb.itemconfig(3,fg="red")        
            window.update()
          else:
            lb.insert(3,item1)  
            window.update()            

          # time_2 = datetime.datetime.now() #程序计时          
          # cha = (time_2 - time_1).seconds
          # print('********导入今日实验耗时%s秒。' % cha)
          # cha1 = cha*2.5
          
            lb.insert(4,'')   
            # lb.insert(3,' 正在处理待分新实验，请稍候！大约需要{:.0f}秒......'.format(cha1))          
            # window.update()
            lb.insert(5,' 开始导入待分新实验到数据库.......')   
            window.update()   
            item2=new_upload()
            # lb.delete(2)   #删除掉进程提示！     
            
            if item2[0][0]=='待':    #如果发生了“待分新实验导入出错，分活儿无效”
              lb.delete(5)
              lb.insert(5,item2[0]) 
              lb.itemconfig(5,fg="red") 
              #20210918新增，导入出错，也要显示一下error
              for i in item2[1]: 
                lb.insert(6,'')                                           
                lb.insert('end',i)  
                lb.itemconfig('end',fg="red")   
                window.update()                 

              window.update()
            else:
              lb.delete(5)
              lb.insert(5,item2[0])  
              window.update()     

              #如果存在error信息，则打印error 信息，后续程序不再继续执行。没有error信息，才会执行后续程序！ 
              for i in item2[1]:
                lb.insert('end',i)  
                lb.itemconfig('end',fg="blue")               
                window.update()
              if item2[2]:   
                for i in item2[2]:                                            
                  lb.insert('end',i)  
                  lb.itemconfig('end',fg="red")               
                  window.update()
              else:
                  # time_3 = datetime.datetime.now() #程序计时
                # cha4 = (time_3 - time_2).seconds
                # print('*******导入待分新实验数据耗时%s秒。' % cha4) 
                # cha5 = cha*4.5 

                lb.insert(6,'')    
                lb.insert(7,' 基于实验优先级及个人测试能力进行实验派发，请稍候！......')
                # lb.itemconfig(5,fg="red")
                window.update()          
                
              
                item3=test_distribe()
                lb.delete(7)                
                lb.insert(7,item3[0])
                lb.insert(8,'')  
                lb.insert(9,item3[1])
                lb.insert(10,'')  
                window.update()

                lb.insert(11,' 分活儿结果写入Excel！......')
                window.update()       

                excel_output.sql_to_excel(ip_address)  
                lb.delete(11)                      
                lb.insert(11," Excel写入完成！")
                lb.insert(12,'') 
                # lb.itemconfig(11,fg="blue")
                window.update()

                # time_4 = datetime.datetime.now() #程序计时
                # cha6 = (time_4 - time_3).seconds
                # total_time=(time_4 - time_1).seconds
                # print('******分派实验耗时%s秒。' % cha6)  
                # print('******本次分活儿总共耗时%s秒。' % total_time)  
                lb.insert(13,' 生成分活儿汇总图表并准备邮件发送......')
                window.update()  
              #邮件发送分活儿结果
                # text=str(item1) + '\n' + str(item2[0]) +'\n' + str(item3[0]) +'\n' + str(item3[1])+'\n' + str(item4)
                text=str(item1) + '\n' + str(item2[0]) +'\n' + str(item3[0]) +'\n' + str(item3[1])+'\n'  
                item5=auto_email(text) 
                
                if item5[1]:   
                  lb.delete(13) 
                  lb.insert(13,item5[1][0])  
                  lb.itemconfig(13,fg="red")        
                  window.update()
                else:
                  lb.delete(13) 
                  lb.insert(13,item5[0])  
                  lb.itemconfig(13,bg="Lime",fg="blue")  
                  lb.insert(14,'') 
                  window.update() 

      else:
        window.destroy()

    def recover():      
      question=tk.messagebox.askokcancel(title='Warning',message='请确保Reliability Test plan表格关闭！否则无法恢复!')
      if question:     
        lb.delete(0,'end') 
        lb.insert(0,'')    
        lb.insert(1,' 开始处理......连接服务器：'+ip_address)   
        window.update()
        time.sleep(1)   
  
        lb.insert(3,' 备份并清理Test Plan 表格，请稍候！......')   
        window.update()
        item = sheet_recover.plan_sheet_recover(ip_address)
        lb.delete(0,'end') 
        lb.insert(0,'')   
        lb.insert(1,item[0])  
        lb.itemconfig(1,fg="blue")
        if item[0][0]=='恢':
          lb.itemconfig(1,fg="red")

        window.update()

    def data_report():    
      #暂时禁止这一功能！
      question=tk.messagebox.askokcancel(title='Warning',message='暂不可用！')
      sys.exit()  
      
    #导入实验到test plan sheets
    def test_import():


      lab_engineers=[]

      def list_selection( ):

        if(var1.get()=='汪焕') and '汪焕' not in lab_engineers:
          lab_engineers.append('汪焕')
        if(var1.get()=='') and '汪焕' in lab_engineers:
          lab_engineers.remove('汪焕')        
        if(var2.get()=='陈凯') and '陈凯' not in lab_engineers:
          lab_engineers.append('陈凯')
        if(var2.get()=='') and '陈凯' in lab_engineers:
          lab_engineers.remove('陈凯')          
        if(var3.get()=='赵俊凯') and '赵俊凯' not in lab_engineers:
          lab_engineers.append('赵俊凯')
        if(var3.get()=='') and '赵俊凯' in lab_engineers:
          lab_engineers.remove('赵俊凯')
        if(var4.get()=='熊春强') and '熊春强' not in lab_engineers:
          lab_engineers.append('熊春强')
        if(var4.get()=='') and '熊春强' in lab_engineers:
          lab_engineers.remove('熊春强')
        if(var5.get()=='张永庆') and '张永庆' not in lab_engineers:
          lab_engineers.append('张永庆')
        if(var5.get()=='') and '张永庆' in lab_engineers:
          lab_engineers.remove('张永庆')
        if(var6.get()=='彭彩虹') and '彭彩虹' not in lab_engineers:
          lab_engineers.append('彭彩虹')
        if(var6.get()=='') and '彭彩虹' in lab_engineers:
          lab_engineers.remove('彭彩虹')

        print(lab_engineers)


      #****** 此初之前放置def read_write(), readiness_check, coefficient_update


      def start_import():
        window_test_import.destroy()
        if lab_engineers !=[]:           

          lb.delete(0,'end')
          lb.insert(0,'')   
          lb.insert(1,' 开始导入，请稍候......')  
          lb.itemconfig(1,fg="black")
          window.update()  
          itemreturn=test_collection.read_write(lab_engineers,ip_address)
          lb.delete(1)
          lb.insert(1,' 实验导入完成！')  
          lb.itemconfig(1,fg="blue")
          window.update()  
          # lb.insert(2,'') 
         
          # print(itemreturn)
          for key in itemreturn:
            lb.insert(2, ' '+key+':'+str(itemreturn[key]))
            lb.itemconfig(2,fg="black")
          window.update()  
          # window_test_import.destroy()  
          #readiness checklist check
          # lb.insert(6,'') 
          lb.insert(6,' 检查新产品Readiness checklist, 请稍候......')           
          lb.itemconfig(lb.size()-1, fg="black")
          window.update()  
          test_collection.readiness_check( )
          lb.delete(6)
          lb.insert(6,' 新产品Readiness checklist 检查完成！')    
          lb.itemconfig(6,fg="blue")
          window.update()    
          #实验系数处理：
          # lb.delete(6,'end')
          # lb.insert(6,'')   
          lb.insert(7,' 开始处理实验系数，请稍候......')  
          lb.itemconfig(7,fg="black")
          window.update()  
          itemreturn2=test_collection.coefficient_update(ip_address)
          lb.delete(7)
          lb.insert(7,' 实验系数处理完成！')    
          lb.itemconfig(7,fg="blue")
          window.update()   
          lb.insert(8, ' '+itemreturn2[0])  
          lb.itemconfig(8,fg="black")
          lb.insert(9, ' '+itemreturn2[1])  
          lb.itemconfig(9,fg="black")
          lb.insert(10,' 全部任务完成！')    
          lb.itemconfig(10,fg="green")          
          window.update()  
    
        else:
          lb.delete(0,'end')
          lb.insert(0,'')   
          lb.insert(1,' 请选择至少一人！')  
          lb.itemconfig(1,fg="red")
          window.update()  

        
      var1=tk.StringVar()
      var2=tk.StringVar()
      var3=tk.StringVar()
      var4=tk.StringVar()
      var5=tk.StringVar()
      var6=tk.StringVar()

      window_test_import=tk.Toplevel(window)
      window_test_import.geometry('350x160')
      window_test_import.title('导入实验计划到Test Plan表格')

      c1=tk.Checkbutton(window_test_import,text='汪焕',variable=var1,
      onvalue='汪焕',offvalue='',command=list_selection)
      c2=tk.Checkbutton(window_test_import,text='陈凯',variable=var2,
      onvalue='陈凯',offvalue='',command=list_selection)  
      c3=tk.Checkbutton(window_test_import,text='赵俊凯',variable=var3,
      onvalue='赵俊凯',offvalue='',command=list_selection)
      c4=tk.Checkbutton(window_test_import,text='熊春强',variable=var4,
      onvalue='熊春强',offvalue='',command=list_selection)  
      c5=tk.Checkbutton(window_test_import,text='张永庆',variable=var5,
      onvalue='张永庆',offvalue='',command=list_selection)     
      c6=tk.Checkbutton(window_test_import,text='彭彩虹',variable=var6,
      onvalue='彭彩虹',offvalue='',command=list_selection)         
      # c6=tk.Checkbutton(window_test_import,state='disabled',text='彭彩虹',variable=var5,
      # onvalue='彭彩虹',offvalue='',command=list_selection)  

      tk.Label(window_test_import,text='勾选导入实验来源:').place(x=10,y=40)
      tk.Label(window_test_import,bg='yellow',font=('Arial',8),width=55, text='只导入当天到达实验！').place(x=10,y=135)
      c1.place(x=150,y=10)
      c2.place(x=150,y=30)
      c3.place(x=150,y=50)
      c4.place(x=150,y=70)
      c5.place(x=230,y=10)
      c6.place(x=230,y=30)

      btn_confirm_days=tk.Button(window_test_import,text='确定',width=9,height=1, command=start_import)
      btn_confirm_days.place(x=150,y=105)



    def weekly_export():    
      def weekly_tests_query(): #定义弹窗中按sign up之后的反应  
        window_check_days.destroy()
        lb.delete(0,'end')
        lb.insert(0,'')   
        lb.insert(1,' 正在查询数据库，请稍候！......')  
        lb.itemconfig(1,fg="blue")
        window.update()  
      
        #获取日期,#定义查询天数  大小写不一样哦！
        # datetime.datetime.now() 表示当前时间
        # datetime.datetime.now().isoweekday() # 返回1-7，代表周一到周日，当前时间所在本周第几天； 
        # datetime.datetime.now().weekday() # 返回的0-6，代表周一到周日

        last_monday=(datetime.datetime.now() - datetime.timedelta(days=datetime.datetime.now().weekday() + 7)).strftime('%Y%m%d') 
        last_sunday=(datetime.datetime.now() - datetime.timedelta(days=datetime.datetime.now().isoweekday())).strftime('%Y%m%d') 
        this_monday=(datetime.date.today()-datetime.timedelta(days=datetime.date.today().weekday())).strftime('%Y%m%d') 
        yesterday=(datetime.datetime.now()+datetime.timedelta(days=-1)).strftime('%Y%m%d') 
        thisday=datetime.datetime.now().isoweekday()  #返回今天是一周的第几天，从1~7 周一到周日

        if thisday==1:
          start_day=last_monday
          end_day=last_sunday
        else:
          start_day=this_monday
          # start_day='20230116'  #导出特殊日期之后的
          end_day=yesterday
        
        print('导出区间：',start_day,end_day)
      
        #建立mysql的连接
        conn = pymysql.connect(host=ip_address,user='labuser',db='exceltodb',
                  password='123456',port=3306,charset='utf8')
        # 获得游标
        cursor = conn.cursor()
        #根据assignment 中的 test_name,更新test_nameid。根据testlist表格。更新的是整个表格
        
        #获取桌面路径
        # path = os.path.join(os.path.expanduser("~"), 'Desktop')  #获取桌面的路径  
        filepath = r'C:\自动分活儿\Weekly tests completed after '+start_day+'.xlsx'
     
        workbook = vb.Workbook(filepath) 
        workbook.save(filepath)
        workbook = vb.load_workbook(filepath)      
    
        sheetname = 'Sincefrom'+start_day     
          
        workbook.create_sheet(sheetname)
        worksheet2 = workbook[sheetname]

        fontstyle = vb.styles.Font(name='Times New Roman',bold=True,size=10)  #标题
        fontstyle1 = vb.styles.Font(name='Arial',bold=True,size=10)    #标题
        fontstyle2 = vb.styles.Font(name='Arial',size=10,color='0000FF')  #正文蓝色
        alignstyle = vb.styles.Alignment(horizontal='center',vertical='center')  #居中  
        alignstyle1 = vb.styles.Alignment(horizontal='left',vertical='center')  #左 
        cellbackground= vb.styles.PatternFill(fill_type='lightGray') 

        worksheet2['A1'] = 'Product'  
        worksheet2['B1'] = 'Test_no'    
        worksheet2['C1'] = 'Vintage'
        worksheet2['D1'] = 'Test_category'
        worksheet2['E1'] = 'Test_name'
        worksheet2['F1'] = 'Status'   
        worksheet2['G1'] = 'Coefficient'    
        worksheet2['H1'] = 'Operator'
        worksheet2['I1'] = 'Lab_Engineer'   
        worksheet2['J1'] = 'Com_Date'
      



        for cell in ['A1','B1','C1','D1','E1','F1','G1','H1','I1','J1']:
          worksheet2[cell].fill = cellbackground
          worksheet2[cell].alignment = alignstyle

        # 定义测试名称列表，提高可读性和可维护性
        valid_test_names = [
            'MDu14', 'MDu31', 'MDu44', 'MDu51', 'MDu66', 'MDu70', 'MDu77',
            'MXc03', 'MXc04', 'MXc06', 'MXc20', 'MXc21', 'MXc22', 'EDu14', 'MDu50'
        ]

        # 定义LIKE模式列表
        like_patterns = [
            'EDu03%', 'EDu06%', 'ALT%', 'MDu30%', 
            'TMO653%', 'ATT11%', 'ATT12%'
        ]

        # 构建基础查询条件（两个查询共享的部分）
        base_conditions = """
            ComDATE >= %s 
            AND ComDATE <= %s 
            AND test_day = total_day 
            AND coefficient >= 0.6 
            AND Vintage != 'EVT' 
            AND (test_category != 'CDVT' OR vintage = '1stORT') 
            AND (
                test_name IN ({}) 
                OR {}
            )
        """.format(
            ', '.join(['%s'] * len(valid_test_names)),  # 生成IN条件占位符
            ' OR '.join(['test_name LIKE %s'] * len(like_patterns))  # 生成LIKE条件
        )

        # 修改计数查询，只选择GROUP BY的列
        count_query = f"""
        SELECT COUNT(*) 
        FROM (
            SELECT LEFT(test_no, 8) 
            FROM assignment 
            WHERE {base_conditions}
            GROUP BY LEFT(test_no, 8)
        ) AS subquery
        """

        # 修改数据查询，使用聚合函数
        data_query = f"""
        SELECT 
            LEFT(test_no, 8) AS test_group,
            MAX(Product) AS Product,
            MAX(Vintage) AS Vintage,
            MAX(Test_category) AS Test_category,
            MAX(Test_name) AS Test_name,
            MAX(STATUS) AS STATUS,
            MAX(coefficient) AS coefficient,
            MAX(operator) AS operator,
            MAX(lab_engineer) AS lab_engineer,
            MAX(comDate) AS comDate
        FROM assignment 
        WHERE {base_conditions}
        GROUP BY LEFT(test_no, 8) 
        ORDER BY MAX(lab_engineer)
        """

        # 构建参数列表（两个查询参数相同）
        query_params = [start_day, end_day] + valid_test_names + like_patterns

        try:
            # 执行计数查询
            cursor.execute(count_query, query_params)
            a = cursor.fetchone()
            
            # 检查结果是否有效
            if a and a[0] is not None:
                print(f'导出实验个数：{a[0]}')
            else:
                print('未找到符合条件的实验数据')
                a = (0,)  # 确保a变量存在，避免后续代码出错
                
            # 执行数据查询
            cursor.execute(data_query, query_params)
            allrows = cursor.fetchall()
            
        except Exception as e:
            print(f"查询执行错误: {e}")
            # 可以选择在这里添加回滚操作
            # connection.rollback()
            raise  # 重新抛出异常，让上层代码处理
        finally:
            # 确保资源被正确关闭（如果需要在其他地方使用cursor，可注释掉）
            # if cursor:
            #     cursor.close()
            pass



        for i in allrows:
          print(i)

    


        for row in range(2,a[0]+2):          
          worksheet2.cell(row,1,value = allrows[row-2][0]).alignment = alignstyle 
          worksheet2.cell(row,1).alignment = alignstyle1 
          worksheet2.cell(row,2,value = allrows[row-2][1]).alignment = alignstyle 
          worksheet2.cell(row,2).alignment = alignstyle1 
          worksheet2.cell(row,3,value = allrows[row-2][2]).alignment = alignstyle     
          worksheet2.cell(row,4,value = allrows[row-2][3]).alignment = alignstyle
          worksheet2.cell(row,4).alignment = alignstyle1  
          worksheet2.cell(row,5,value = allrows[row-2][4]).alignment = alignstyle 
          worksheet2.cell(row,5).alignment = alignstyle1 
          worksheet2.cell(row,6,value = allrows[row-2][5]).alignment = alignstyle 
          worksheet2.cell(row,7,value = allrows[row-2][6]).alignment = alignstyle 
          worksheet2.cell(row,8,value = allrows[row-2][7]).alignment = alignstyle 
          worksheet2.cell(row,9,value = allrows[row-2][8]).alignment = alignstyle 
          worksheet2.cell(row,10,value = allrows[row-2][9]).alignment = alignstyle 


        worksheet2['A1'].font = fontstyle1
        worksheet2['B1'].font = fontstyle1
        worksheet2['C1'].font = fontstyle1
        worksheet2['D1'].font = fontstyle1
        worksheet2['E1'].font = fontstyle1
        worksheet2['F1'].font = fontstyle1 
        worksheet2['G1'].font = fontstyle1
        worksheet2['H1'].font = fontstyle1
        worksheet2['I1'].font = fontstyle1
        worksheet2['J1'].font = fontstyle1

        
      

        worksheet2.column_dimensions['A'].width = 12
        worksheet2.column_dimensions['B'].width = 11
        worksheet2.column_dimensions['C'].width = 8
        worksheet2.column_dimensions['D'].width = 10
        worksheet2.column_dimensions['E'].width = 13
        worksheet2.column_dimensions['F'].width = 14
        worksheet2.column_dimensions['G'].width = 14
        worksheet2.column_dimensions['H'].width = 11
        worksheet2.column_dimensions['I'].width = 11
        worksheet2.column_dimensions['J'].width = 14




        worksheet2.row_dimensions[1].height =20
        worksheet2.auto_filter.ref = "A1:J1"

        workbook.active=worksheet2
        workbook.save(filepath)
        notice='报告已输出到自动分活儿文件夹中！'

        lb.delete(1)
        lb.insert(1,notice)
        lb.itemconfig(1,fg="blue")
        window.update()            
        


      window_check_days=tk.Toplevel(window)
      window_check_days.geometry('400x90')
      window_check_days.title('Weekly Test List导出')


      days_enter=tk.StringVar()
      tk.Label(window_check_days,text='       * 周一将导出从上周一到周日期间完成的，重要的Weekly实验清单;\n 其它时间导出本周一到昨天之间完成的实验清单。',bg='yellow',font=('Arial',8)).place(x=10,y=10)

      btn_cancel=tk.Button(window_check_days,text='取消',width=8,height=1, command=sw_exit)
      btn_confirm=tk.Button(window_check_days,text='确定',width=8,height=1, command=weekly_tests_query)
      btn_cancel.place(x=120,y=50)
      btn_confirm.place(x=210,y=50)


    def ALT_huanren():    
      def go(*args):  #处理事件，*args表示可变参数  #可以放在Win=thinter.TK语句之后
        print(comboxlist.get()) #打印选中的值
        global input
        input=comboxlist.get()    

      def select_start_date(): #定义弹窗中按sign up之后的反应  
        window_check_days.destroy()
        lb.delete(0,'end')
        lb.insert(0,'')   
        lb.insert(1,' 正在处理，请稍候！......')  
        lb.itemconfig(1,fg="blue")
        window.update()  
  
      
        #3.建立mysql的连接
        conn = pymysql.connect(host=ip_address,user='labuser',db='exceltodb',
                password='123456',port=3306,charset='utf8')
        # 获得游标
        cursor = conn.cursor()
      
        #1.获取当前日期  
        todaycolumn=datetime.datetime.now().strftime('%Y-%m-%d')    
        today=datetime.datetime.strptime(todaycolumn, '%Y-%m-%d')
        stardate = datetime.datetime.strptime(input, '%Y-%m-%d') #字符串日期变换成日期格式
        summary=[]
        while stardate <= today:
          # print(stardate)
          timestart=stardate
          stardate = stardate + relativedelta.relativedelta(months=3)
          timeend=stardate
          print(timestart,timeend)

      
          # timestart='2022-10-01'
          # timeend='2023-01-01'

          testnumberlist=[]
          cursor.execute('''SELECT * FROM assignment WHERE Test_name='ALT 2019' AND startdate >=%s AND startdate <%s ''',(timestart,timeend))
          alltests=cursor.fetchall()
          for i in alltests:
              if i[2] not in testnumberlist:
                  testnumberlist.append(i[2])
          # print(testnumberlist)
          n=0
          m=0
          p=0
          for test_number in testnumberlist:
              cursor.execute('''select test_name, status,operator,startdate,comdate,test_day from assignment where test_no=%s''',test_number)
              testinfors=cursor.fetchall()
              operator_in_eachday=[]     
              for i in testinfors:
                  if i[2]!='':  #去掉停止日
                      operator_in_eachday.append(i[2])
              if len(operator_in_eachday)>=5: #去掉特殊的少于5天周期的实验            
                  n=n+1
                  huanrentimes=len(dict(Counter(operator_in_eachday)))-1
                  if huanrentimes>0:
                      p=p+1
                      m=m+huanrentimes
                      print(p,test_number,'换人次数：',huanrentimes,operator_in_eachday,i[3],'~',i[4])
          if n>0:
              percent=int(m/n*100)
          else:
              percent=0
          print(' ')
          print('***.',timestart,'到',timeend,'   实验总数：',n,'   换人次数：',m,',  占比：', percent,'%')
          charator='*'+str(timestart)[:10]+'到'+str(timeend)[:10]+'   ALT换人占比：'+ str(percent)+'%'
          summary.append(charator)
          print('----------------------------------------------------------------------------------------')

        print('\n','Summary:')
        for i in summary:
            print(i)
        print('\n')




        notice='  输出完毕！'

        lb.delete(1)
        lb.insert(1,notice)
        lb.itemconfig(1,fg="blue")
        window.update()            
        


      window_check_days=tk.Toplevel(window)
      window_check_days.geometry('400x90')
      window_check_days.title('Weekly Test List导出')


      tk.Label(window_check_days,text='选择统计开始时间：',font=('Arial',10)).place(x=95,y=15)  


      comvalue=tkinter.StringVar()#窗体自带的文本，新建一个值
      comboxlist=ttk.Combobox(window_check_days,textvariable=comvalue,width = 12, height = 18 ) #初始化
      comboxlist["values"]=("2023-01-01","2023-04-01","2023-07-01","2023-10-01","2024-01-01","2024-04-01","2024-07-01","2024-10-01","2025-01-01","2025-04-01")
      comboxlist["state"]='readonly' #不可输入其它内容
      comboxlist.current(0) #选择第一个
      comboxlist.bind("<<ComboboxSelected>>",go) #绑定事件,(下拉列表框被选中时，绑定go()函数)
      comboxlist.place(x=210,y=15)


      btn_cancel=tk.Button(window_check_days,text='取消',width=8,height=1, command=sw_exit)
      btn_confirm=tk.Button(window_check_days,text='确定',width=8,height=1, command=select_start_date)
      btn_cancel.place(x=120,y=50)
      btn_confirm.place(x=210,y=50)

    def ALT_huanren_hours():  
      pass      



    def plan_tests():
      #暂时禁止这一功能！
      question=tk.messagebox.askokcancel(title='Warning',message='暂不可用！')
      sys.exit()  

    def db_import():
      #暂时禁止这一功能！
      question=tk.messagebox.askokcancel(title='Warning',message='暂不可用！')
      sys.exit()   

         

    def db_recovery():
      #暂时禁止这一功能！
      question=tk.messagebox.askokcancel(title='Warning',message='暂不可用！')
      sys.exit()  


    def workload_trend_chart():      
      lb.delete(0,'end')
      lb.insert(0,'')  
      lb.insert(1,' 正在进行查询，请稍候！......')  
      lb.itemconfig(1,fg="blue")
      window.update() 
      charts.workload_chart(ip_address)        

      msg='  任务完成!'       
      lb.delete(1,'end')
      # lb.insert(0,'')  
      lb.insert(1,msg)
      lb.itemconfig(1,fg="blue")   
      window.update()  


    def exam_result_to_plan():
      pass

    def help_me():  
      os.startfile(r'\\10.114.184.9\Tcc_Rel\04 Task_Assignment\备份\分活后备份\自动分活儿汇总20230321.pptx')   

    def sw_exit():           
      # window.destroy()
      os.remove('icon.gif')
      sys.exit()   
      

    #分活儿后的实验查询
    def get_name():
      name=E1.get()
      result=test_query(name)
      lb.delete(0,'end')
      lb.insert(0,' ')
      lb.insert(1,result[0])
      lb.insert(2,'')
      #下面3行替代后面的若干行，不会因为list超限报错了
      i=len(result)      #看一下列表长度，再循环插入 
      for j in range(3,i+2):        
        lb.insert(j,result[j-2])      


    def edu03_query():
      result=esd_query()
      lb.delete(0,'end')
      lb.insert(0,' ')
      lb.insert(1,result[0])
      lb.itemconfig(1,fg="blue")
      lb.insert(2,'')
      #下面3行替代后面的若干行，不会因为list超限报错了
      i=len(result)      #看一下列表长度，再循环插入 
      for j in range(3,i+2):        
        lb.insert(j,result[j-2])    

    
    def testplan_assignment_check( ):    
      pass





    #三个buttons
    btn_proceed=tk.Button(window,text='执行',width=11,height=1,bg='#C0C0C0', fg='#DC143C',command=sw_proceed)
    btn_proceed.place(x=52,y=140)
    btn_proceed=tk.Button(window,text='恢复',width=11,height=1,bg='#C0C0C0',command=recover)
    btn_proceed.place(x=52,y=180)

    btn_proceed=tk.Button(window,text='实验查询',width=10,height=1,bg='#C0C0C0',command=get_name)
    btn_proceed.place(x=348,y=462)
    btn_proceed=tk.Button(window,text='多日预排',width=10,height=1,bg='#C0C0C0',command=plan_tests )
    btn_proceed.place(x=152,y=140)
    btn_proceed=tk.Button(window,text='使用帮助',width=10,height=1,bg='#C0C0C0',command=help_me)
    btn_proceed.place(x=422,y=140)
    btn_proceed=tk.Button(window,text='报表汇总',width=10,height=1,bg='#C0C0C0',command=data_report)
    btn_proceed.place(x=332,y=180)
    btn_proceed=tk.Button(window,text='DB更新',width=10,height=1,bg='#C0C0C0',command=db_import )
    btn_proceed.place(x=242,y=140)
    btn_proceed=tk.Button(window,text='DB恢复',width=10,height=1,bg='#C0C0C0',command=db_recovery)
    btn_proceed.place(x=242,y=180)
    btn_exit=tk.Button(window,text='实验导入',width=10,height=1,bg='#C0C0C0',command=test_import )
    btn_exit.place(x=152,y=180)
    btn_exit=tk.Button(window,text='ESD实验',width=10,height=1,bg='#C0C0C0',command=edu03_query)
    btn_exit.place(x=332,y=140)
    btn_exit=tk.Button(window,text='退出',width=10,height=1,bg='#C0C0C0',command=sw_exit)
    btn_exit.place(x=422,y=180)
 
  
    #定义Execute菜单
    menubar=tk.Menu(window,)
    execute_menu=tk.Menu(menubar,tearoff=0,background='lightblue', foreground='black')  #tearoff指是否分开，1能分开
    menubar.add_cascade(label='Execute',menu=execute_menu)
    execute_menu.add_command(label='执行分活儿', command=sw_proceed)
    execute_menu.add_command(label='恢复',command=recover)
    execute_menu.add_command(label='实验导入',command=test_import)
    execute_menu.add_separator() #加一条线
    execute_menu.add_command(label='退出',command=sw_exit)

    #定义Report菜单
    report_menu=tk.Menu(menubar,tearoff=0,background='lightblue', foreground='black')  
    menubar.add_cascade(label='Report',menu=report_menu)
    report_menu.add_command(label='Weekly导出',command=weekly_export)
    report_menu.add_command(label='ALT换人统计',command=ALT_huanren)
    report_menu.add_command(label='工作量走势图',command=workload_trend_chart)
     
    
    #定义Help菜单
    help_menu=tk.Menu(menubar,tearoff=0,background='lightblue', foreground='black')  
    menubar.add_cascade(label='Help',menu=help_menu)
    help_menu.add_command(label='帮助文件',command=help_me)
   
 
    window.config(menu=menubar)
    window.mainloop()


if __name__=="__main__": 
  # ip_address='localhost'
  ip_address='10.114.183.17' 
  # ip_address='10.114.183.55'   
  version='V3.25.06'  
  releasedate='2025/08/05'   
  print('Server address:',ip_address)
  windows()
 